Why is DATEONLY() returning the wrong date?

John C Murray

A far simpler workaround:

https://community.smartsheet.com/post/editdiscussion/109948

Stefan

Hi @John C Murray ,

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

https://community.smartsheet.com/discussion/109948/solution-day-and-dateonly-functions-ignoring-locale-information

And thanks for your solution :-)

Stefan

John C Murray

Thanks for that Stefan, much appreciated

Will Olson

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

S E

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.