Lookup field not pulling the correct date

#1

Hello! I have a lookup field in one table to copy over a date from the connected record. This has been working fine, except for one entry that for some reason is displaying the lookup date as the day after the actual date from the connected record. Any thoughts on why this might be happening?

Thanks so much!

0 Likes

#2

‘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:

  1. 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
  1. 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.²

  1. 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…
  2. 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.’)
0 Likes

#3

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!

0 Likes