Referencing another field in formula results in the date from yesterday

[Abhijeet_Rastog]

I have a date field by name Due Date. Another field by name Formula is a simple formula as follows:-

IF({Due Date} != "", {Due Date}, SET_TIMEZONE(TODAY(), 'America/Los_Angeles'))

Hence, if Due Date is empty, it simply sets the current date.

If Due Date is not empty, it somehow sets a day which is 1 day behind. I’ve no clue why this is happening.
I have verified that the "include same timezone (GMT)… " option is not checked in the Formula field.

Any help is really appreciated. I’m unable to add image/link to the post so can’t share the exact base. :frowning:

Thanks

[JonathanBowen]

Hi @Abhijeet_Rastogi - your formula works fine for me:

I used the slightly amended formula of:

IF({Due Date}, {Due Date}, SET_TIMEZONE(TODAY(), 'America/Los_Angeles'))

where, on the first argument of the IF you can say “IF {Due Date}” which translates as “if Due Date exists”. But both formula versions work OK for me.

Take a look here for instructions to share a base - if we can have a look directly at what you are doing someone might be able to help.

Airtable - Support

Creating a base share link or a view share link

As an alternative to adding individual people as collaborators, you can create a read-only share link that allows anyone with the link to view (but not edit) an Airtable base or a specific table vi...

JB

[Abhijeet_Rastog]

Hi @JonathanBowen, probably because I’m a new member, I don’t have permissions to post a link here so I can’t share the base here.

I tried your formula too, which is definitely cleaner, it still shows me the older date. If it matters, I changed the timezone’s recently but my local time on the laptop is set to Los_Angeles.

[Justin_Barrett]

You might need to set the timezone for your due date as well. Even though it should just be a straight copy of the reference date, Airtable will sometimes assume a different timezone when referencing it. Try this and see if it works:

SET_TIMEZONE(IF({Due Date}, {Due Date}, TODAY()), 'America/Los_Angeles')

When testing this myself, I had to turn the GMT switch on for the formula, otherwise TODAY() was a day off. I swear, of all the features of Airtable, its handling of time and dates can often be the most confusing.

[Abhijeet_Rastog]

Hi @Justin_Barrett,

Thanks for suggesting the GMT option. For now, I had to turn it on for this to be working. The SET_TIMEZONE doesn’t work correctly if the GMT property is not set.

Abhijeet