How to reflect correct date from "dateonly" formula.

Tyler G.

Hello,

In the attached image, I have an auto column "Modified"

Smartsheet Help.jpg

base on user entry / modification of a row. The "Date" column is formatted as a date entry with a formula of "=dateonly([Modified]@row)". The formula works, however, the 04/11/22 dates are not reflecting the proper date of 04/10/22. The only thing I can think is the time is what is making the date advance a day.

Any help would be appreciated.

Tyler

Paul Newcome

The time zone bit is an unfortunately known issue. First you have to figure out what time that change occurs. For this example I will say 7PM. From there we use an IF statement to subtract 1 from the date if the hour is greater than or equal to 7 and "PM" is present.

=DATEONLY(Modified@row) - IF(AND(VALUE(SUBSTITUTE(MID(Modified@row, 10, 2), ":", "")) >= 7, FIND("PM", Modified@row) > 0), 1, 0)

Jeff Reisman

@Tyler G. I could write a thesis called "Smartsheet and Time Zones: A Study in Huh? What? How?"

I have encountered more issues with system date columns and time zones than I care to mention.

My preferred method to get around this would be to use an Automation Workflow, triggered when a row is changed, to record the date in your Date column. This Date will be in your time zone as the owner of the sheet. So if you're in Eastern time and the change is made at 1am on 4/11/22, then 4/11/22 would be recorded; whereas with the system modified date, that's actually stored in Pacific time, so 4/10/22 gets stored.

Paul Newcome

The time zone bit is an unfortunately known issue. First you have to figure out what time that change occurs. For this example I will say 7PM. From there we use an IF statement to subtract 1 from the date if the hour is greater than or equal to 7 and "PM" is present.

=DATEONLY(Modified@row) - IF(AND(VALUE(SUBSTITUTE(MID(Modified@row, 10, 2), ":", "")) >= 7, FIND("PM", Modified@row) > 0), 1, 0)

Jeff Reisman

@Tyler G. I could write a thesis called "Smartsheet and Time Zones: A Study in Huh? What? How?"

I have encountered more issues with system date columns and time zones than I care to mention.

My preferred method to get around this would be to use an Automation Workflow, triggered when a row is changed, to record the date in your Date column. This Date will be in your time zone as the owner of the sheet. So if you're in Eastern time and the change is made at 1am on 4/11/22, then 4/11/22 would be recorded; whereas with the system modified date, that's actually stored in Pacific time, so 4/10/22 gets stored.

Tyler G.

Thank you @Paul Newcome and @Jeff Reisman for the follow-ups. I think we can make something work between these two resolutions.

Paul Newcome

Happy to help. 👍️