TextToColumns Date Conversion Fails for June, July and September

[chonc76]

Running Excel 365 Version 2401.

I've tried to import a sheet with text dates and used the TextToColumns (Delimited, Date Format) to convert and I noticed not all dates converted. Upon further testing I created a column of 2 years worth of dates in the format month-day-year eg, January-01-2023. I then copied to a text editor (Windows formatting, UTF-8) and pasted back to a clean sheet. Upon conversion as described above all dates converted except for dates in June, July and September which remain "general"; all other dates convert to "date" format.

Any help appreciated.

 

[shift-del]

In the last step in text-to-columns you can set a format for each column.

Even better is import via Power Query where you can set data types (even with locale) before loading to the worksheet.

 

[chonc76]

In the last step in text-to-columns you can set a format for each column.

Even better is import via Power Query where you can set data types (even with locale) before loading to the worksheet.

Yes, that was done with MDY but like I said it doesn’t convert the months specified.

 

[shift-del]

Did you try import with PQ?

 

[chonc76]

Did you try import with PQ?

I did but I’m not looking for alternative way more interested why the TTC function isn’t working.

 

[chonc76]

I did but I’m not looking for alternative way more interested why the TTC function isn’t working.

PQ works fine btw.

 

[Alex Blakenburg]

I did but I’m not looking for alternative way more interested why the TTC function isn’t working.

And to 'add insult to the injury' FlashFill handles it fine

 

[Dave Patton]

"I did but I’m not looking for alternative way more interested why the TTC function isn’t working."

I thought the TTC would work until I tried it.
You didn't request a workaround but I included one. N.B. If the months were all 3 characters like Jan TTC would work.
This was with a system using International Date format dd-mm-yy. If USA Regional settings do not work try MONTH(TEXTBEFORE(A2:A12,"-")&"-1")

Dates and Time 2024.xlsm
	A						G
1
2	January-01-2023						01-Jan-23
3	February-01-2023						01-Feb-23
4	March-01-2023						01-Mar-23
5	April-01-2023						01-Apr-23
6	May-01-2023						01-May-23
7	June-01-2023						01-Jun-23
8	July-01-2023						01-Jul-23
9	August-01-2023						01-Aug-23
10	September-01-2023						01-Sep-23
11	November-01-2023						01-Nov-23
12	December-01-2023						01-Dec-23
13
2h

Cell Formulas
Range	Formula
G2:G12	G2	=DATE(RIGHT(A2:A12,4),MONTH(1&TEXTBEFORE(A2:A12,"-")),RIGHT(TEXTBEFORE(A2:A12,"-",-1),2))
Dynamic array formulas.