TODAY(), SET_TIMEZONE(), and WEEKDAY() are killing me

[ucmavnerd]

I am trying to convert TODAY() to the WEEKDAY() and then to the single letter for that day of the week. I need to match that to a data feed that provides the day of the week as a letter. Here is my formula:

SWITCH(WEEKDAY(SET_TIMEZONE(TODAY(),'America/Los_Angeles')),0,"U",1,"M",2,"T",3,"W",4,"R",5,"F",6,"S")

The problem is that it is returning the wrong WEEKDAY() numeral. If I do this:

SET_TIMEZONE(TODAY(),'America/Los_Angeles')

I get the correct day. And if I do this:

WEEKDAY(TODAY())

I get the correct weekday.

But as soon as I combine these ideas it’s returning the value for yesterday. So, assuming we are using today as 2/2/2022 I’d expect to get WEEKDAY() of 3 and my SWITCH() would change it to a W. But what I am getting returned is “2” which is turning into a T.

Thoughts?

[ScottWorld]

I’m not really 100% sure about any of this, but I’m thinking that the problem could be that when you specify TODAY(), the time that it uses is 12:00am midnight in your local time zone, regardless of whether you specified Los Angeles or not.

So let’s say that you’re in a different time zone such as New York time zone, it will take “today at midnight” for your New York time zone, which is “yesterday at 9pm” Los Angeles time zone.

However, if you’re already in Los Angeles, then I really have no idea what the problem could be.

I think that a possible workaround for this problem could be to use NOW() instead of TODAY() in your formula. That would take the time right now instead of using midnight as the time.

You’d probably still have the same exact problem when it gets close to midnight, but at least it could resolve the problem for a majority of the time.

But yeah, I’m not really sure, so hopefully some other people will have some input on this!

[ucmavnerd]

Interesting.

I always thought NOW() was when you put the command into the formula and that it didn’t update dynamicly. I’ll try it.

EDIT: I’ll probably have to wait for the GMT rollover at 4PM PDT to see if it works. Cause it’s hard to tell since Airtable does GMT across all time fields for consistency (which is right, I just can’t trick the clock to see what happens. :grinning_face_with_sweat: )