Lookup field not pulling the correct date

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

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

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!