Convert text to date

Anthea

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/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.

Any thoughts?

Thankyou

Paul Newcome

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

Paul Newcome

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"

Anthea

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!

Paul Newcome

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

Anthea

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

Paul Newcome

Glad you were able to get it sorted. 👍️

SPBops

Very helpful! Thank you.

How would we adjust this if the text in some rows include the year and some don't?

For example:

Row 1: "01/01/24 This is text"

Row 2: "01/01 This is text"

Row 3: "1/1 This is text"

I tried an automation to add a date when a cell is changed, but it changed all 288 rows in the sheet to today's date :(