Date & Time Read Incorrectly for Formulas

[Tashina_O]

I have a Date/Time field that feeds into a formula field to pull the date from it. It used to work perfectly well, now that I have added the time option to it as well though there is an issue with it sorted dates wrong.

As an example I have a task that is listed with the date 1/19/2021 7:00pm but when I put it into the formula it reads it as 01/20/2021. If I change the time to 1/19/2021 7:00am it reads it as 01/19/2021.

It does read the Date/Time correctly in Calendar view.

Is there anything I can do to ensure it reads the Date/Time field correctly for the formula? It is important for grouping reasons.

The formula I am currently using is: IF({Due On}=BLANK(), “N/A”, DATETIME_FORMAT({Due On}, ‘L’))

[ScottWorld · Accepted solution]

Welcome to the community, @Tashina_O!

This is a tricky one, because this is a time zone issue.

The very first thing to check is to make sure that your GMT time zone setting is set the same for both fields.

If that doesn’t work, you might need to dive into some time zone formulas.

I’ve also created a training video & sample base, which serve as a deep dive into time zones:

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Tashina_O]

This worked perfectly! I never even thought time zones would have been an issue as it’s just me accessing this in the same timezone I created everything in. Thank you so much for your swift response and the simple to follow tutorial.

[ScottWorld]

Glad I could help! :slightly_smiling_face:

[OpsDir_Cassie]

Hi @ScottWorld ! I have a similar issue but I already have my time zone toggled on:

I posted separately about this, but curious if you can identify what my issue is? 

Here's the link to my other post: https://community.airtable.com/t5/formulas/date-amp-time-formula-incorrect/td-p/142546 

[ScottWorld]

Hi @OpsDir_Cassie, time zone problems are extremely tricky to figure out. There are also several bugs in Airtable regarding time zones, too. Please post your problem in my forums at TableForums.com, and the experts will try to help you out.