Help

DATETIME_PARSE returns a day before the actual date

Topic Labels: Dates & Timezones
1698 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Daria_B
4 - Data Explorer
4 - Data Explorer

I’m using a table that takes a text (email body) and returns various elements, such as date.

So I have a date written in one column like this:
November 14, 2020
December 10, 2020

I use several formulas to get this info, such as FIND, MID and LEN. The formula in this first column is: MID({Email text},{Find Start Cut Off Date},{Length Cutoff Date})

Then in another column, I use DATETIME_PARSE to convert this info into a date. And for whatever reason, it returns a day before the actual date that’s in the first column. The output is:
13 November, 2020
9 December, 2020

What am I doing wrong?

3 Replies 3

Welcome to the community, @Daria_B!

It’s likely a time zone issue. You can probably quickly fix it by using the SET_TIMEZONE function, but you’ll also want to check the “GMT Time Zone” settings in all of your date fields.

Also, I would check out my recent comments on time zones & formulas in this thread:

And, for a deep dive into time zones, you can checkout my sample base & training video here:

Daria_B
4 - Data Explorer
4 - Data Explorer

But the original field doesn’t have any timezone at all, it’s just text. So why does it convert to a timezone at all? I’m in EDT timezone and I have checked all the fields to make sure the GMT checkbox is unchecked.

I used the SET_TIMEZONE function to set the timezone in EDT, however, I believe it considers the text field as if it’s in GMT so it converts it to EDT. So I either have to use GMT in all fields or I can’t make it work.

I have some automations set up based on the date, so converting to GMT will not work for me.

Honestly, the fact that Airtable doesn’t have the timezone feature figured out yet is pretty clearly shows that the company doesn’t understand their users.

They do have it figured out, which is why you have so many different options for controlling the time zone. This is one of the few areas of the product that enables a ton of user control. Maybe it could be improved if they tacked on the time zone list to the date field, but their current methodology works well and also offers lots of flexibility.

If you don’t specify a time, it will consider midnight as the time.