Convert Text to Date Format

JJLewis

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!

Kelly Moore

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)))

JJLewis

Kelly Moore

Thank you! That did the trick...

Kelly Moore

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)))

JJLewis

Kelly Moore

Thank you! That did the trick...