Help

Re: Need quick assist with TIME_ZONE

1425 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Doug_Gregan
6 - Interface Innovator
6 - Interface Innovator

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.

15 Replies 15

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
6 - Interface Innovator
6 - Interface Innovator

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?

Yeah - first thought - what’s your timezone?

That formula from @ScottWorld seems to work fine:

image

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.

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:

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.

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.

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.

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

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.

Right, I’ll stick with it for now. If you’d be willing to instruct on the literal solution, I’d love to learn.

Holy crap - I just lost a day! I’m losing more than my mind apparently.

Why not just parse the date off of Now() set to local time?

image

The local time toggle in the user interface only affects the display of the time in the user interface. It doesn’t change the fact that the underlying value used in formula fields is GMT/UTC.

In Airtable, the concept of local time only exists in a specific client. Formulas are not client specific, and thus date/time values are always GMT unless you use SET_TIMEZONE or do some other workarounds like manually adding an offset.

Justin and I have both created various systems for getting timezone offsets. I’m just one my phone and it is too hard to look up the specific posts.

Thanks, @kuovonne!

@Doug_Gregan It sounds like my sample base would be of most help to you, since it specifically handles these time offsets.

If you have some thoughts/samples, that would be great. I appreciate your time and input.