Convert text to date
I have a column containing text, which contains a date in MM/DD/YY format, followed by a longer string of text.
For example, one of the cells contains "07/26/22-G"
I want to populate another column, with column type = date, with the date, so that I can order by date.
I tried this using this formula
=MID([Test1]@row, 1, 8)
It does populate 07/26/22 in my column as expected, but it is not treated as a date, so I cannot sort or filter by it.
I also tried this formula, to explicitly convert the string to a date, also thinking it might be confused by the fact that the month appears before the day:
=DATE(MID([Test1]@row, 7, 2), MID([Test1]@row, 1, 2), MID([Test1]@row, 4, 2))
but got #INVALID DATA TYPE
I have checked (and double checked!) that my column type is set to Date.
Help Article Resources
Check out the Formula Handbook template!