Incorrect results extracting date from system Created (Date) column

SJ Sellers

I have a system column of Created (Date). This is [Snapshot Date/Time] in the screenshot below. I need another column that should reflect the DATE part of the system column. I am getting unexpected results in the date-only column.

In the attached screenshot - you can see for both 4/21 and 4/22, the formula returns 4/22.

Formulas:

temptest: =[Snapshot Date/Time]@row

Snapshot Date =DATEONLY([Snapshot Date/Time]@row)

Jeff Reisman

@SJ Sellers

Smartsheet and time zones.... Ugh. What time zone are you in?

When formulas evaluate system date columns (Created (Date), Modified (Date)), at least here in North America, it's not seeing the local time that appears in those fields on your screen. Instead, it's reading it in Pacific time, where the servers are located.

So, if you're in Eastern time, when you see 4/22/22 12:01 AM, what the formula sees is 4/21/22 9:01 PM. At the same time, when you use the TODAY() function, the system sees that in Eastern time. So at 4/22/22 12:02 AM Eastern, the system evaluates a snapshot time of 4/22/22 12:01 AM as being the previous day.

For three years I've been hoping that Smartsheet could make times be consistent with our set time zone across all functions.

You can take advantage of the Record a Date automation to set the current date based on changes to a row or a time being reached. That will record the date as it is in your regional settings.

SJ Sellers

I am doing some further experiments and this has got to be a bug. I added a column to calculate Workdays between today and the system date. The formula returns 1 for both Apr 21 and Apr 22. Today cannot be 1 day away from both today and yesterday.

When typing the date in manually, Apr 21 and Apr 22 are 2 and 1 days away respectively.

Formulas:

Workdays computed from System Date: =NETWORKDAY([Snapshot Date/Time]@row, TODAY())

Workdays computed from manual date: =NETWORKDAY([Manually typed Date]@row, TODAY())

Jeff Reisman

@SJ Sellers

Smartsheet and time zones.... Ugh. What time zone are you in?

When formulas evaluate system date columns (Created (Date), Modified (Date)), at least here in North America, it's not seeing the local time that appears in those fields on your screen. Instead, it's reading it in Pacific time, where the servers are located.

So, if you're in Eastern time, when you see 4/22/22 12:01 AM, what the formula sees is 4/21/22 9:01 PM. At the same time, when you use the TODAY() function, the system sees that in Eastern time. So at 4/22/22 12:02 AM Eastern, the system evaluates a snapshot time of 4/22/22 12:01 AM as being the previous day.

For three years I've been hoping that Smartsheet could make times be consistent with our set time zone across all functions.

You can take advantage of the Record a Date automation to set the current date based on changes to a row or a time being reached. That will record the date as it is in your regional settings.

SJ Sellers

Hi @Jeff Reisman -

Thank you for the response. But now I'm even more confused than ever. Could it be that Smartsheet is evaluating dates based on Greenwich Mean Time?

I am in the Central Time Zone and my regional settings reflect that. So in my screenshot, 7:01 PM CT would actually be 5:01 PM PT on the same day. In GMT, it would be 12:01 AM GMT on the following day - which seems to line up with what I am observing..

After I submitted this question, I instead created this formula =DATE(VALUE(MID([Snapshot Date/Time]@row, 7, 2)), VALUE(LEFT([Snapshot Date/Time]@row, 2)), VALUE(MID([Snapshot Date/Time]@row, 4, 2))).

I thought about using automation to record a date - but frankly, I don't trust automations. I have had more than one occasion where one didn't run, and I saw a number of people submit questions to Community just today saying their automation that had been running fine for a year has suddenly failed.

The timezone inconsistency is.... infuriating, to say the very least.

Jeff Reisman

@SJ Sellers It probably just depends on which way your DNS or Smartsheet's load balancing takes you. Most of the time ours hits California, but then I've scratched my head and realized it hit GMT for a while.

Recurring time-based automations seem to be pretty dependable. I use them for moving rows nightly out of very busy sheets, and haven't had one get missed in 3+ years.