Why is DATEONLY() returning the wrong date?

Hello All,

Does anyone understand the behavior of the highlighted cell below? I believe it should be displaying "08/18/20".


Best Answers



  • Michele Thomas
    Michele Thomas ✭✭✭✭

    Hi Brian,

    This is an odd thing. Try


    That may help for it to select the correct row...possibly.

  • Hi Michele,

    Thanks for the suggestion, but that didn't work either.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It very possibly has to do with Time Zones. I have seen this a handful of times elsewhere throughout this Community. If you do a search for posts related to time zones you should be able to find a few different options for solutions.

  • Thanks Paul, I can definitely see this as being the issue. I'll try to do some more testing and let you know what I find.

  • Wow thanks Paul! This solution seems to do the trick (after I tune in the time values, of course). I do have a question about it: does "=LEFT(Date@row,8)" do the trick or is there an instance where that wouldn't work?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L@123 I feel like this had been tried in a previous post and it was still pulling the wrong date because of how it was stored on the back-end. That's why I went with the solution I used. My solution mostly ignores the date by looking at the time instead.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/20/20

    I see what you are saying, I didn't look closely enough at your formula. Yeah I would double check to make sure that the left actually does correctly populate.

    We really need some major enhancements to time functionality in smartsheet....

  • Stefan
    Stefan ✭✭✭✭✭✭

    Yes, time zone handling in Smartsheet really needs a global solution!

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • BD
    edited 01/08/21

    Hello @Brian Cianciolo . Did your solution of using the Left Function work? Currently this is my workaround, but, I was curious to see if you had any issues using the Left Function. Thank you.

  • Brian Cianciolo
    Brian Cianciolo ✭✭✭
    edited 01/08/21

    Hi @BD . The left solution is working fine for me. If there is an instance where it hasn't worked; I have yet to notice it.

  • BD

    Thanks Brian.

    In the end, I had to use the below formula from Paul above in order to get =yearday function to work. . Thanks Paul.

    =DATEONLY(Date@row) - IF(AND(VALUE(MID(Date@row, 10, FIND(":", Date@row) - 10)) >= 7, FIND("P", Date@row)> 0), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • I am in Pacific Time Zone and am running into the same issue with DATEONLY().

    After some trial and error I found the date to be off by 7 hours, so the DATEONLY() will give the date at 0:00 GMT.

    I added another AND() expression to @Paul Newcome's formula to account for this but also to account for the 12pm hour.

    =DATEONLY(Created@row) - IF(AND(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 5, VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) < 12, FIND("P", Created@row) > 0), 1)

    If it doesn't work for you, just try adjusting the number in the first expression (5 in my example)

    Hope that helps.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!