DateOnly YearDay off a day Time Zone Question

edited 01/08/21 in Formulas and Functions
01/08/21 Edited 01/08/21

Hello. I have a question about the DateOnly and YearDay functions. Please see below (Pic 1). All dates after 7pm gets moved to the next day for the DateOnly and YearDay functions. I am assuming, not 100%, this is a Time Zone issue as I live in UTC -5 hours.(Pic 2). I saw a post (Pic3) that said that Smartsheet captures dates and times in UTC but displays the value accordingly to your personal setting. Since I live in UTC time zone and my personal settings are set to UTC, What else could cause this? Thank you.

Computer Settings

Previous post in 2018

Thank you.

Best Answer


  • BDBD

    I also would like to add my settings for SmartSheet:

    Thank you.

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Hi @BD ,

    Looks to me like the timezone issue that many people have posted about. @Paul Newcome is the time expert. His response to the post below may lead you to a solution.

    I'm curious to see what others think.


  • KDMKDM ✭✭✭✭✭

    Hi BD

    Yes, it's a timezone problem - I'm also UTC-5 and I first discovered it, just like you, when I noticed working formulas would return wrong value exactly at 7pm. Usually the timezone doesn't affect formulas except, for example, comparing the timezone date to Today() during the time period of UTC-0 until your timezone's midnight. So for the central timezone that's our 7pm-midnight.

    I have verified that the new Record a Date automation is based on actual time zone. This might be a workaround for you if you have a trigger that you can capture. The output of this automation is a date, not a date stamp (with time). Interestingly if one extracts the time from the datestamps it remains in the correct timezone - it's just the date portion that has affected my formulas.



  • BDBD

    Thank you @Mark Cronk and @KDM for your replies.

    Thanks Mark, I just replied to the other thread as I am using the same Left function workaround as the OP in that thread.

    Has anyone had any issues using =LEFT([email protected], 8)? So far so good but I do not have enough data to verify.

  • BDBD

    The above Left function did not work for when I tried to using the YearDay function. However, thanks to Paul's answer in another thread from link above, I ended up using the below formula

    =YEARDAY(DATEONLY([email protected]) - IF(AND(VALUE(MID([email protected], 10, FIND(":", [email protected]) - 10)) >= 7, FIND("P", [email protected]) > 0), 1))

    Thanks Everyone.

  • Mark CronkMark Cronk ✭✭✭✭✭

    Excellent. Glad you found a solution. Thank you for adding to the Community.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Glad you were able to find a working solution. 👍️

  • BDBD
    edited 01/18/21

    Hello @Paul Newcome . I am realizing that this option does not always work for entries around 2 pm - 7pm. Sometimes, but not always, I can get it to work by:

    • Cut and past the same formula in the cell.
    • Sometimes reopening the sheet. This can work 1-2 hrs after entry.
    • wait till 7 pm and it would be working correctly. It may not work the next day till 7pm or another option above.

    I am using the Yearday function as criteria for a Sumifs formula.

    I also noticed that:

    • multiple days are affected. However, it would work one day and time and not another day and time.
    • The YearDay would show the correct day in the cell but not always work in the sumifs formula.
    • Entries before 12pm will always work.

    Any help is really appreciated. Thank you

  • BDBD

    Hello @Paul Newcome . Does this only occur for a cell / column that used the auto number created (Date) option? Is time associated with a basic column date property? Thank you.

  • BDBD

    So far the formula below that was originally posted by Kennedy has been working for me for the past 10 days with no issues. Thank you. @Kennedy Stomps

    =DATE(YEAR([email protected]), MONTH([email protected]), VALUE(VALUE(MID([email protected], 4, 2))))

Sign In or Register to comment.