DateOnly YearDay off a day Time Zone Question

BD
BD ✭✭
edited 01/08/21 in Formulas and Functions

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

Answers

  • BD
    BD ✭✭

    I also would like to add my settings for SmartSheet:

    Thank you.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

    cheers,

    Kelly

  • BD
    BD ✭✭

    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(Date@row, 8)? So far so good but I do not have enough data to verify.

  • BD
    BD ✭✭

    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(Date@row) - IF(AND(VALUE(MID(Date@row, 10, FIND(":", Date@row) - 10)) >= 7, FIND("P", Date@row) > 0), 1))


    Thanks Everyone.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • BD
    BD ✭✭
    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

  • BD
    BD ✭✭

    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.

  • BD
    BD ✭✭

    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(Date@row), MONTH(Date@row), VALUE(VALUE(MID(Date@row, 4, 2))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!