CSV - Converting General Text to Dates

[mdqs]

Hi,

I have a small CSV in which the information in the first column (dates) imports as, e.g., Thu Jul 01 2021 in 'General' number format. I'd like to convert information in this column to dates of any recognizable style (short, long, does not matter). Changing number format does not alter the text whatsoever.

I've also tried text-to-columns and deliminating by spaces, then concatenating to create dates. No luck.

Any help is appreciated! Sorry if this has been asked before.

 

[rlv01]

If you are sure that the text dates will always be of this form: "Thu Jul 01 2021", then you can use a formula in a new column to convert the text to a excel date. One example.

=DATEVALUE(SUBSTITUTE(MONTH(DATEVALUE(MID(A1,5,3)&1))& MID(A1,8,99)," ","-"))

If the text date format varies, then it becomes harder.

 

[Dave Patton]

Ensure that there are no extraneous text such as blanks before or after the date

Date and Time 4.xlsm
	A	B
1
2	Thu Jul 01 2021	1-Jul-21
3
1f

Cell Formulas
Range	Formula
B2	B2	=DATE(RIGHT(A2,4),MONTH(MID(A2,5,3)&1),MID(A2,9,2))

 

[mdqs]

Beautiful. Thank you both so much. Challenge solved.

 

[JEC]

=DATEVALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)))