Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

Tags:

Best Answers

«1

Answers

  • ✭✭✭✭

    Hi Brian,

    This is an odd thing. Try

    =DATEONLY(Date@row)

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

  • Hi Michele,

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

  • Community Champion

    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?

  • Community Champion

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

  • Community Champion
    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....

  • Community Champion

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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

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

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

  • ✭✭✭

    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)

  • 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!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6