Convert Text to Date
I am looking for help to convert 20230818 ['Max Date' text formatted column] to read 08/18/2023 in the ['Last Activity Date' date formatted column]. However, the formula that I am using returns a value of #INCORRECT ARGUMENT SET.
=DATE(VALUE(MID([Max Date]@row, 5, 2), (VALUE(RIGHT([Max Date]@row, 2), (VALUE(LEFT([Max Date]@row, 4)))))))
Best Answer
-
Hi @Heather P.,
Try this:
=RIGHT((LEFT([Max Date]@row, 6)), 2) + "/" + RIGHT([Max Date]@row, 2) + "/" + LEFT([Max Date]@row, 4)
I tested and here are the results:
Answers
-
Hi @Heather P.
This should work:
=DATE(VALUE(LEFT([Max Date]@row, 4)), VALUE(MID([Max Date]@row, 5, 2)), VALUE((RIGHT([Max Date]@row, 2))))
Your formula will not work as the middle bit of the formula is trying to calculate the month - there isn't an 18th month of the year. The format of the date itself will be based on Smartsheet settings.
-
Try this in the Last Activity Date (Which should be a DATE Column):
=DATE(VALUE(LEFT([Max Date]@row, 4)), VALUE(MID([Max Date]@row, 5, 2)), VALUE(RIGHT([Max Date]@row, 2)))
DATE (YYYY,MM,DD)
Need to wrap VALUE() around to convert string to number for the DATE() function
Your version was:
DATE (MM,DD,YYYY)
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Hi @Heather P.,
Try this:
=RIGHT((LEFT([Max Date]@row, 6)), 2) + "/" + RIGHT([Max Date]@row, 2) + "/" + LEFT([Max Date]@row, 4)
I tested and here are the results:
-
Hi @Ray Lindstrom - that worked! Thank you so much!
-
So glad to hear it. Happy to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!