Formula not Pulling Time Stamp Time post 7:00PM Central Standard, Why?

Christian Graf

I have a [Time] Column with a formula that uses SUBSTITUTE() and DATEONLY() to pull only the time from a timestamp column.

=SUBSTITUTE([Start Time (Timestamp)]@row, DATEONLY([Start Time (Timestamp)]@row), "")

It works fine for any timestamp that occurs prior to 7:00PM, after that time, it pulls the date as well.

I also have an [Actual Date] Column that has the formula:

=[Start Time (Timestamp)]@row

This gives me the wrong date after 7:00PM even though the [Time] Column gives me the correct date. Huh?

The only thing I can thing of is that something is wonky with Smartsheet itself, and internal formulas for functions are maybe set to different time zones?

Jeff Reisman

@Christian Graf Are you in Central Time Zone, with Daylight Savings Time? That would be UTC-5:00.

Smartsheet stores system date/time in UTC and just presents it to you in your regional settings. So 3/22/23 at 7:00pm is 3/23/23 at 12am, the next day. That explains getting the wrong date after 7pm.

It's very weird about the substitute. I can tell you that just running DATEONLY in a text column on a date value that falls in the next day UTC results in an #INVALID COLUMN VALUE, unless you add + "" to convert it to text. Even then, however, the DATEONLY will return the date in UTC, not CDT.

There's obviously a mismatch due to one function working against the value at the presentation level (2/21/23 in my example) and the value at the data level (2/22/23 in my example.)

I have asked Smartsheet to address this by giving us system-generated time stamps in the time zone we specify, defaulting to the sheet owner's regional setting but have time zone be a setting the sheet owner can select.

Now - workaround: Simplify the whole thing by converting the visible value over to text, and taking the MID value:

=MID([Start Time (Timestamp)]@row +"", 10, 8)

Jeff Reisman

@Christian Graf Are you in Central Time Zone, with Daylight Savings Time? That would be UTC-5:00.

Smartsheet stores system date/time in UTC and just presents it to you in your regional settings. So 3/22/23 at 7:00pm is 3/23/23 at 12am, the next day. That explains getting the wrong date after 7pm.

It's very weird about the substitute. I can tell you that just running DATEONLY in a text column on a date value that falls in the next day UTC results in an #INVALID COLUMN VALUE, unless you add + "" to convert it to text. Even then, however, the DATEONLY will return the date in UTC, not CDT.

There's obviously a mismatch due to one function working against the value at the presentation level (2/21/23 in my example) and the value at the data level (2/22/23 in my example.)

I have asked Smartsheet to address this by giving us system-generated time stamps in the time zone we specify, defaulting to the sheet owner's regional setting but have time zone be a setting the sheet owner can select.

Now - workaround: Simplify the whole thing by converting the visible value over to text, and taking the MID value:

=MID([Start Time (Timestamp)]@row +"", 10, 8)

Christian Graf

Top Notch! Thank you @Jeff Reisman

Your workaround works perfectly!