‘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.’)
