Why is DATEONLY() returning the wrong date?

08/19/20
Accepted

Hello All,

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

Tags:

Best Answers

Answers

  • Michele ThomasMichele Thomas ✭✭✭✭✭

    Hi Brian,

    This is an odd thing. Try

    =DATEONLY([email protected])

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

  • Brian CiancioloBrian Cianciolo ✭✭✭✭✭

    Hi Michele,

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

  • Paul NewcomePaul 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.

    thinkspi.com

  • Brian CiancioloBrian Cianciolo ✭✭✭✭✭

    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.

  • Brian CiancioloBrian Cianciolo ✭✭✭✭✭

    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([email protected],8)" do the trick or is there an instance where that wouldn't work?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @[email protected] 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.

    thinkspi.com

  • L_123L_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....

  • StefanStefan ✭✭✭

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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • BDBD
    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 CiancioloBrian 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.

  • BDBD

    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([email protected]) - IF(AND(VALUE(MID([email protected], 10, FIND(":", [email protected]) - 10)) >= 7, FIND("P", [email protected])> 0), 1)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • 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([email protected]) - IF(AND(VALUE(MID([email protected], 10, FIND(":", [email protected]) - 10)) >= 5, VALUE(MID([email protected], 10, FIND(":", [email protected]) - 10)) < 12, FIND("P", [email protected]) > 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.

Sign In or Register to comment.