+4Hello. 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.
Best answer by kuovonne
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.
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.
+29Bill 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.
+4Thanks both. In this instance the import problem was solved by simply enabling the time field on the date field.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
