Choosing the time zone used for Date and Time functions

[Chris_Rutledge]

I am in Seattle, using my local time zone, Pacific Standard Time (GMT -8). One of my columns, ‘Start Time’, contains a Date value of “1/2/2016 10:00”, but when I reference it in a separate column using

DATETIME_FORMAT({Start Date}, ‘MM/DD/YY HH:mm’)

the time displayed is 8 hours ahead (18:00). The same thing occurs for any other times I reference.

I see that I could use SET_TIMEZONE, but do I need to use this whenever I want to have the results of a formula in my time zone? Is this a setting I can change in my account or base, anywhere?

[sunny]

Thank you, @Justin_Barrett and @ScottWorld. My table has past, present, and future dates. I am sorting to have records with today’s date on top. Couldn’t find another way to do this so I have an IF TODAY() = 1, 0 condition; and I am then sorting descending 1 --> 0. This works fine except when it’s evening time and AT thinks it’s the next day (a few hours too early) for me.
I use this table alone right now (while still figuring out some kinks) and other collaborators in the future will also be in my timezone.

[ScottWorld]

Switch your formula to NOW() instead of TODAY(), and also be sure to turn OFF the GMT formatting option for all of your date fields… including the formula field.

[sunny]

Thank you, @ScottWorld. Switched to NOW() and made sure GMT is turned off in the date field. Did a system time change and it still works. Will report back tonight in case it breaks. Thank you! :smiling_face_with_halo:

[sunny]

@ScottWorld sorry for being mia past few days. The change to NOW() didn’t work :frowning: It’s 6.45p here and IF NOW() = 1, 0 already turned to 0

[ScottWorld]

That’s not a valid formula. What are you trying to test for in your formula?

Even if you had a valid formula that looked something like IF(NOW()=1,0,1), that wouldn’t make sense either, because it would always result in 0.

[Justin_Barrett]

sunny:

I am sorting to have records with today’s date on top.

@ScottWorld I think that’s what @sunny is trying to do. As you said, that’s not the way to go about it.

@sunny Based on the formulas you listed, it seems that you’re somehow under the impression that both TODAY() and NOW() are designed to automatically check elsewhere in the record to see if it matches the current date, but that’s not the case. They both return the current date. The difference is that TODAY() arbitrarily throws midnight on for the time (assuming you only want to compare the date), whereas NOW() returns the current day and time at the point it was executed. On top of that, the date and/or time returned for both is based on GMT (contrary to what I told you previously, @ScottWorld. I’ll dig into the depths of this another time.)

Long story short: your formula needs to compare the date in your date field against either TODAY() or NOW(). However, don’t compare them as actual dates because of the GMT difference in those two functions. The easiest way I’ve found is to compare the formatted dates. Specifically, the formatted version of your manually-entered date—which will always be based on your local timezone—against either TODAY()/NOW() after forcing them to your local timezone using SET_TIMEZONE(). You can also skip the IF() wrapper, because the comparison itself will return 1 or 0 depending on whether the formatted dates match (1) or don’t (0):

DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "L")

Obviously replace the timezone reference with the one for your local area.

Now, if you want to avoid an error if you haven’t entered a date, I do suggest tossing an IF() wrapper around it, forcing the output to 0 if there’s no date.

IF(Date, DATETIME_FORMAT(Date, "L") = DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "L"), 0)

[sunny]

Sorry for being lazy, the formula is IF(DATETIME_DIFF(NOW(),Date,‘days’) = 0, “1”, “0”). This works fine (returns a 1) till 4.59p Pacific. At 5p (GMT 12am), it returns a 0 for everything except tomorrow’s records.

[Justin_Barrett]

I saw you were writing your post while I was writing mine. Long story short, I suggest using one of my formulas above. Comparing date differences still won’t fix the fact that NOW() is based on GMT, whereas your {Date} field is based on your local timezone. The only ways to fix the time shift compared to GMT is by formatting, or manually shifting NOW() by a certain number of hours to convert it to your local time before comparing to {Date}.

[sunny]

@Justin_Barrett Thank you! This works :raised_hands:
IF(Date, DATETIME_FORMAT(Date, “L”) = DATETIME_FORMAT(SET_TIMEZONE(NOW(), “America/Los_Angeles”), “L”), 0)

[Alix_C]

I have a simple ask when I create a date field. It should be my local time ie Central.
How can I achieve it? No formula as I was a selectable date widget on the cell. This is where, date is being selected for the very first time.
If the timezone is an account-level setting point me out in the right direction. Thanks