Date Formulas based off Created column calculating from / converting to GMT

Kyle Chipman

Hi all,

I've just discovered that date formulas pulling from the Created column will convert the date and time to the equivalent Greenwich Mean Time date and time. For our central time zone office, any submissions that occur at 7:00pm or later will show a formula-driven date of 12:00am GMT, which is a day after the Created field. Please see the screenshot below (red fields are 7:00pm CST or later, blue fields are 6:59pm CST or earlier)

Is anyone aware if time zones can be enforced at the sheet level, or is everything that runs as a formula based off GMT at a systems level? Want to confirm before I carry out a bunch of text parsing formulas to force the correct dates in calculations.

Thanks,

Kyle

Paul Newcome

This is a known issue where the Created date/timestamp is being stored on the backend in that particular time zone.

The most reliable solution I have come up with is to grab the DATEONLY then use an IF/AND to evaluate the hour an am/pm and subtract 1 accordingly.

So if anything after 7 PM is showing as the next day, it would look like this...

=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1))>= 7, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1))< 12), 1)

Kyle Chipman

I went ahead and parsed the text to create a Created@row or WORKDAY(Created@row, -1) calculation.

However, an interesting wrinkle - the parsed text will show the correct time zone depending on whether I'm working from my remote Chicago machine or my local Portland machine. For reference, I used =SUBSTITUTE(Created@row, "M", "m") to build a string from the Created column. There might be a better way to do this.

REMOTE CHICAGO (CENTRAL) MACHINE

Weird, it re-calculated in the pacific time zone for a moment off my local machine, but then reverted to central and will not repeat. Was trying to recreate to get a screenshot. More a curiosity than anything else.

If I'm going about this in a boneheaded way and there's a simpler path forward, just holler. Thanks much :)

Paul Newcome

This is a known issue where the Created date/timestamp is being stored on the backend in that particular time zone.

The most reliable solution I have come up with is to grab the DATEONLY then use an IF/AND to evaluate the hour an am/pm and subtract 1 accordingly.

So if anything after 7 PM is showing as the next day, it would look like this...

=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1))>= 7, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1))< 12), 1)

Kyle Chipman

Thanks for the response Paul. Your formula looks good; super helpful to have your confirmation of the known GMT-centric logging.

Have a rocking day :)

Paul Newcome

Sure thing and happy to help. 👍️

Jake Gustafson

Thanks @Paul Newcome 2021. I found this and believe it is helping me deal with some date issues with my night shift employees creating records late in the evening and the timestamps configure to the next day's date, which does not behave nicely with other formulas. With a limited number of data points, I believe that I've got the formula dialed in.

#powerofcommunity

Jake Gustafson

Hello, I've had a day of this formula, but I'm still running into some issues. I'm not 100% sure I understand how it is working and have toyed with one of the parameters. Some items work fine, while others are showing an incorrect date. Below are two variations, I guess the 2nd one (the original code from this thread) has fewer errors, so I'll leave that one in there for now. Not sure if it has anything to do with the Date format type, my time zone (US Central), or anything else that I might be overlooking. Any help or suggestions would be greatly appreciated. Thanks.

=DATEONLY([Created Timestamp]@row) - IF(AND(FIND("P", [Created Timestamp]@row) > 0, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, 1)) >= 1, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, 1)) < 12), 1)

=DATEONLY([Created Timestamp]@row) - IF(AND(FIND("P", [Created Timestamp]@row) > 0, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, 1)) >= 7, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, 1)) < 12), 1)

Paul Newcome

@Jake Gustafson The bold portion is the hour in which the date becomes wrong. So if everything after 7pm is showing as the next day then you would want that to be a 7. If everything after 4pm then change it to a 4.

Basically we start with the DATEONLY function then use an IF/AND to evaluate the time. If it is "PM", and the hour is less than 12 (to rule out noon) and greater than #, subtract 1 from the date.

Jake Gustafson

Thanks @Paul Newcome. The only record at the moment that I'm having an issue with is from 10:38 PM. It continues to round to the next day. I've got a 4:57 PM, 6:50 PM, 8:58 PM that are all working as I'd expect them. I'm not sure if the double digit hour characters (10:XX PM & 11:XX PM) are throwing something off. If I adjust the bold value to ≥9 or ≥10, it still doesn't get me what I'm looking for.

Paul Newcome

Looks like I made a mistake in my formula. When pulling the hour for the calculation, I am only pulling a single digit. This should pull everything between the space and the colon to account for both 1 or two digit hours...

=DATEONLY([Created Timestamp]@row) - IF(AND(FIND("P", [Created Timestamp]@row) > 0, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, FIND(":", [Created Timestamp]@row) - (FIND(" ", [Created Timestamp]@row) + 1))) >= 7, VALUE(MID([Created Timestamp]@row, FIND(" ", [Created Timestamp]@row) + 1, FIND(":", [Created Timestamp]@row) - (FIND(" ", [Created Timestamp]@row) + 1))) < 12), 1)

My apologies to you and anyone else that ran into this issue.

Jake Gustafson

Thanks @Paul Newcome. Glad you were able to catch it. All my rows appear to be functioning properly now. I should get a few more submissions from the night shift throughout the remainder of the week to validate that things are still on track. Appreciate the prompt analysis and updated solution.

Paul Newcome

@Jake Gustafson Happy to help. 👍️

denslen

OK, I thought this would resolve my issue as well but it doesn't work correctly. It was working well for the Date until my GMT time rolled over to the AM again where it returned tomorrow's GMT Date in place of my current local date (10/12/22 4:49 AM GMT should still be 10/11/22 9:49PM. I'll presume at this point that my issue may have to do with setting my time preference to GMT and then trying to get a calculated local time in 24Hr. format. I will try to convert back to local time tomorrow AM and start working to get a calculated UTC time. Too burnt out right now.

I really like Smartsheet for some things but this time bug sure is a problem.

@Paul Newcome I certainly can appreciate the effort it took to create your formula to extract the Date adjusted for a time zone because I am having a hell of a time deciphering it.

Paul Newcome

@denslen The basic logic behind the formula is that we pull the hour out. We compare that to the hour that the date becomes incorrect (in Jake's case 7PM) so we say that if the hour is greater than or equal to 7 and less than 12 (keeps it from adjusting on noon) and the time is PM, then subtract one day from the date. Essentially... If it is after a certain hour specific to your time zone then subtract a day.

jomansfi87106

I was able to use this successfully, thanks for sharing that (complicated) formula!