ICS date import off by a day

Dan_Smith · ‎Apr 20, 2020

Hello. I’m having a problem importing ICS data, where the start and end date from the ICS file are importing as one day earlier in Airtable.

The ICS file is set to Europe/London, but not sure if the timezone would affect the days themselves.

Can anyone suggest how I investigate further or fix this? I have a few thousands of records to import from a Google calendar.

kuovonne · ‎Apr 21, 2020

Bill gave you an excellent explanation of how Airtable stores dates under the hood.

Here is a suggestion for investigating if it is a timezone issue:

Customize the date field in Airtable so that it also shows a time field. Then experiment with turning on and off the “Use same time zone” toggle and see how that affects the date/times. If the date/time is correct using one of those settings, then you probably have a time zone issue.

If you are able to confirm that it is a time zone issue, decide if you want to keep the dates as they are stored and adjust the “Use the same time zone” toggle, or if you want to actually change the stored values. If you want to change the stored values, you can add the offset to the date/time using a temporary formula field with the DATEADD() formula function, then copy the new values back to the original field and deleting the temporary field.

See Solution in Thread

Bill_French · ‎Apr 21, 2020

Yes, it can affect the date and there are other nuances about dates that can be problematic. For example, all dates are stored in Airtable are UTC values, not the date/time in the upload file. Airtable itself will render the dates transformed from UTC into your current locale.

Lastly, you need to know the date format in the imported records. This can be UTC or the local time in the location where the exported ICS file was created. Or, it could be UTC.

So, imagine you have an upload record with the actual date/time in BST (wait, you’re on BDT (British Daylight Savings now, right?) If the export was created before March 29th, and imported after March 29th, that could affect it as well.

Whatever the case, it’s nearly impossible to help without actual data examples.

kuovonne · ‎Apr 21, 2020

Bill gave you an excellent explanation of how Airtable stores dates under the hood.

Here is a suggestion for investigating if it is a timezone issue:

Customize the date field in Airtable so that it also shows a time field. Then experiment with turning on and off the “Use same time zone” toggle and see how that affects the date/times. If the date/time is correct using one of those settings, then you probably have a time zone issue.

If you are able to confirm that it is a time zone issue, decide if you want to keep the dates as they are stored and adjust the “Use the same time zone” toggle, or if you want to actually change the stored values. If you want to change the stored values, you can add the offset to the date/time using a temporary formula field with the DATEADD() formula function, then copy the new values back to the original field and deleting the temporary field.

Dan_Smith · ‎Apr 22, 2020

Thanks both. In this instance the import problem was solved by simply enabling the time field on the date field.