Converting text to date

[polska2180]

I searched for a solution but couldn't find one. I have an output form system where the dates look like the picture attached. The data is text which I want to convert to a date but no matter what combination of formulas i find none of the get the job done. I'm not sure if its the extra spaces or what. Either way I included text to column converter image to better show how the data looks. Is there a formula that will do the job...thanks.

 

[steve the fish]

This may work depending on what your date system is. It presumes the month is always 3 letters.

=0+MID(SUBSTITUTE(A1," ",LEFT(A1,3)),7,11)

 

[jasonb75]

Have you tried parsing the dates with text to columns? If it doesn't work then that would suggest imported data with invalid characters that would need to be removed before the text can be converted to proper dates.

 

[mrshl9898]

Hard to tell if additional spaces are in there.

Try:

DATE(RIGHT(D2,4),MONTH(DATEVALUE(LEFT(D2,3)&1)),NUMBERVALUE(MID(D2,5,2)))

 

[polska2180]

This may work depending on what your date system is. It presumes the month is always 3 letters.

=0+MID(SUBSTITUTE(A1," ",LEFT(A1,3)),7,11)

Ty I think maybe the spaces created an issue but I'd don't work.

 

[Dave Patton]

Please post an extract of your data with the forum's XL2BB.
Format the date to your preference.

try

Date and Time.xlsm
	A	B
12	Jan 7 2021	2021Jan07
13	Jan 28 2021	2021Jan28
14	Nov
1b

Cell Formulas
Range	Formula
B12:B13	B12	=DATE(RIGHT(A12,4),MONTH(1&LEFT(A12,3)),MID(A12,5,2))