Convert Text to Date Format
I am trying to copy a text column written out as "Thursday 07/29/2021" into another cell with a date format "07/29/21" in order to use that cell as a reference. I have tried the VALUE formula but have not been able to get it to work...
Thanks for you help!
Best Answers

Hey @JJLewis
Try this. The FIND function returns the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string. The DATE function requires a syntax of DATE(YYYY, MM, DD)
=DATE(VALUE(RIGHT([your text column]@row, 4)), VALUE(MID([your text column]@row, FIND("/", [your text column]@row)  2, 2)), VALUE(MID([your text column]@row, FIND("/", [your text column]@row) + 1, 2)))

Thank you! That did the trick...
Answers

Hey @JJLewis
Try this. The FIND function returns the starting position (a number) of whatever it is looking for, in this case a "/". The formula uses the number it finds as a starting position in the text string. The DATE function requires a syntax of DATE(YYYY, MM, DD)
=DATE(VALUE(RIGHT([your text column]@row, 4)), VALUE(MID([your text column]@row, FIND("/", [your text column]@row)  2, 2)), VALUE(MID([your text column]@row, FIND("/", [your text column]@row) + 1, 2)))

Thank you! That did the trick...
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 349 Global Discussions
 198 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
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!