Convert text to date
Hi
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/22G"
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.
Any thoughts?
Thankyou
Best Answer

Yes. My apologies. I meant to include that bit as well. the DATE function requires numeric values but the MID function outputs text values.
Answers

The second formula is going to be your base. The only issue with it is that you are only pulling two digits for the year when you need to have 4 in the DATE function.
Try changing
MID([Test1]@row, 7, 2)
to
VALUE("20" + MID([Test1]@row, 7, 2))
This way you are outputting into the year portion "2022" instead of just "22"

Thanks very much! I tried that, but was still getting #INVALID DATA TYPE... but then I put VALUE around all 3 of the parameters, it worked!
This is the formula I have now:
=DATE(VALUE("20" + MID([Test1]@row, 7, 2)), VALUE(MID([Test1]@row, 1, 2)), VALUE(MID([Test1]@row, 4, 2)))
Many thanks indeed!

Yes. My apologies. I meant to include that bit as well. the DATE function requires numeric values but the MID function outputs text values.

Thanks very much, yes that makes sense to me now.

Help Article Resources
Categories
Check out the Formula Handbook template!