‘Off by a day’ errors in date files is almost always caused by the ‘Use UMT’ toggle being set (or not being set, depending). Even if you don’t have ‘include a timestamp’ toggled, the GMT switch may be flipped. First step, check that.
Two things to keep in mind, though:
- Toggling ‘Use UMT’ does not change the representation of the datetime, as the ‘formatting’ options do: It changes the underlying data. That is to say, if you have a date field showing, say,
1/1/2019 8:00am
, and you toggle on ‘use UMT,’ the display doesn’t change to show what 1/1/2019 8:00am
in your local time would be in UMT; instead, the field value is shifted forward or backward the number of hours your timezone is offset from UMT.¹
For example, I’m on Pacific Standard Time, which is -0800 (8 hours behind) UMT. If I define two fields, {Date}
and {DateF}
, where {Date}
is a date field and {DateF}
is a formula field set to equal the value of {Date}
, here’s what I get with different combinations of settings:
UMT on |
Date |
DateF |
neither |
1/13/2019 7:30am |
1/13/2019 7:30am |
Date |
1/13/2019 7:30am |
1/12/2019 11:30pm |
Date, DateF |
1/13/2019 7:30am |
1/13/2019 7:30am |
DateF |
1/13/2019 7:30am |
1/13/2019 3:30pm |
-
Under some circumstances — typically involving comparison of date fields with calculated dates — I’ve not been able to remedy the issue using the UMT toggle. Fortunately (and, most likely, the officially correct way to deal with date fields in general), the
SET_TIMEZONE()
function allows you to force a standard timezone for all your dates.²
- Actually, I’m not sure this merits the scary boldface warning, as most people with whom I’ve discussed this have understood how the toggle works intuitively. I guess I must be slow, though, because it took nearly a year before I twigged the toggle was dealing with absolute, rather than relative, times…
- Well, actually, it allows you to force each individual date to follow a specified timezone — but as long as you force each date to use the same timezone, you can consider it the ‘standard.’)
Thank you so much! That was definitely the problem. I appreciate your help, and now I know how I need to set up my datetime fields in the future!