Need quick assist with TIME_ZONE

[Doug_Gregan]

Hello,

I have a big favor to ask, which is basically, can someone help fix this formula? I am creating a Yes/No flag to display content using the TODAY() function, but I need it to determine TODAY() by Eastern Standard Time. This is what I have and its not working…

IF(SET_TIMEZONE(TODAY(), ‘America/New_York’) = {Prayer Point Date}, “Yes”, “No”)

The conditional piece works great, it’s the TIME_ZONE that’s a bust. All help is appreciated.

[ScottWorld]

SET_TIMEZONE must be used in conjunction with DATETIME_FORMAT, so it would need to look something along the lines of this:

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/New_York'), 'M/D/YYYY h:mma')

[Doug_Gregan]

ScottWorld:

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/New_York'), 'M/D/YYYY h:mma')

Thank you very much for your time and help. So… when I use this:

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/New_York'), 'M/D/YYYY')

It’s coming up as yesterday. Thoughts?

[Bill_French]

Doug_Gregan:

It’s coming up as yesterday. Thoughts?

Yeah - first thought - what’s your timezone?

That formula from @ScottWorld seems to work fine:

[Doug_Gregan]

Hi @Bill.French . My timezone is ‘America/New_York’ and unless I’m losing my mind, yours isn’t right either. Today is the third.

I also am not sure in my “check against this date” Field what format I should use to actually get a match. I use it as a display element, so I’d like it to look like “June 3, 2022.” But I don’t know what corresponding format to use in my formula.

[ScottWorld]

Ah okay, so I see what the problem is here.

Airtable acts REALLY strange when it comes to time zones, which is why I created this sample base & training video:

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...

However, your issue is a completely different issue than what I solved above.

When you specify TODAY() in a formula like the above formulas without specifying a time, Airtable chooses midnight on your behalf as the default time. But — here’s the catch — Airtable chooses midnight as if you were in the GMT time zone before applying the New York Time Zone conversion! :crazy_face:

So, when you tell Airtable that you want New York time zone without specifying a time, Airtable starts with midnight GMT, and then subtracts 4 hours from there to get New York. Makes very little sense. :man_shrugging:

So what you’re actually seeing on screen is 8:00pm yesterday night.

In other words, it’s showing you “today at midnight in the GMT Time Zone” which is the equivalent of “yesterday at 8:00pm in the New York Time Zone”.

But if you just need to see today’s date — with no worries about the time — perhaps you can just use TODAY() all on its own in the formula, and be sure to turn on GMT in the date formatting options.

[Doug_Gregan]

OK, Scott. I’m going to go with that and see if it changes correctly at midnight. I just don’t want it to change too early.

I really appreciate your time. Have a great day! Thank you, @Bill.French, for your time, too.

[ScottWorld]

Oh, right, that might be a problem. Lol.

We will probably need to come up with a more complicated formula to solve your problem. But I won’t be back at my computer until tomorrow to try to play with this.

In the meantime, take a look at my sample base above. It might shed some partial light on this situation.

[Doug_Gregan]

Ah… OK. No problem. I started watching your tutorial yesterday, but haven’t had time to finish it. I’ll try to get more time in on that, too.

Thank you, again! Have a good night. - Doug

[ScottWorld]

If you just needed “today” to display during normal business hours, it probably wouldn’t be an issue. But since you’re looking for precision timing, that’s where the complexity comes in.