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


I have checked (and double checked!) that my column type is set to Date.

Any thoughts?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!