Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Referencing another field in formula results in the date from yesterday

Topic Labels: Dates & Timezones
2499 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Abhijeet_Rastog
5 - Automation Enthusiast
5 - Automation Enthusiast

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

4 Replies 4

Hi @Abhijeet_Rastogi - your formula works fine for me:

Screenshot 2019-04-18 at 07.59.01.png

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.

JB

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.

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.

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