How to reflect correct date from "dateonly" formula.



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

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.


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!