Convert to a Date
So, I have what seems to be a simple date question, but I am unable to find a solution. I need to convert text into a date.
Example: 2023-09-01
Need to convert to 9/1/2023
Any ideas?
Best Answers
-
Hi @Jeremy Norman,
Something like this should work if your text date has the same format:
=DATE(VALUE(LEFT([Text column]@row, 4)), VALUE(MID([Text column]@row, 6, 2)), VALUE(RIGHT([Text column]@row, 2)))
If it didn't have the leading 0s for day/month then you could add in FINDs to get the relevant start points. I'm also not 100% sure which date format you're using so if month/day are the wrong way round then switch the 2nd and 3rd parts of the formula around!
Hope this helps, but if you've any problems/questions then just post! 🙂
-
See below formula. Change the bolded references to match your data
=DATE(VALUE(LEFT(Text@row, 4)), VALUE(MID(Text@row, 6, 2)), VALUE(RIGHT(Text@row, 2)))
Answers
-
Hi @Jeremy Norman,
Something like this should work if your text date has the same format:
=DATE(VALUE(LEFT([Text column]@row, 4)), VALUE(MID([Text column]@row, 6, 2)), VALUE(RIGHT([Text column]@row, 2)))
If it didn't have the leading 0s for day/month then you could add in FINDs to get the relevant start points. I'm also not 100% sure which date format you're using so if month/day are the wrong way round then switch the 2nd and 3rd parts of the formula around!
Hope this helps, but if you've any problems/questions then just post! 🙂
-
See below formula. Change the bolded references to match your data
=DATE(VALUE(LEFT(Text@row, 4)), VALUE(MID(Text@row, 6, 2)), VALUE(RIGHT(Text@row, 2)))
-
Worked perfectly. Thank you!
-
Thank you also....@Leibel S
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!