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?

2»

Answers

  • Community Champion

    Hi @John C Murray ,

    you link does not work, here is the corrected link:

    SOLUTION: DAY and DATEONLY functions ignoring locale information

    And thanks for your solution :-)

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • ✭✭✭✭

    Thanks for that Stefan, much appreciated

  • OMG...this was driving me crazy! Thank you for the solutions @Paul Newcome and @James Pedersen ! I can't believe this is still a thing but knowing that DATEONLY uses GMT is helpful. I copy/pasted James's solution

    --

    =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)

    --

    and it worked like a charm. Many many many thanks!


    Will

  • Replying to this thread because it helped me!

    Just as an FYI, as of Feb 2025:

    =LEFT(Date@row,8)

    Works if you ONLY need to display the date. However, I had a problem where referencing the cell with the formula will result in invalid data type (even if the column is set as a date).

    I ended up using:

    =DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2)))

    Which allowed me to both display and use it in other formulas.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions