Why is DATEONLY() returning the wrong date?
Answers
-
A far simpler workaround:
-
Hi @John C Murray ,
you link does not work, here is the corrected link:
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
Categories
Check out the Formula Handbook template!