Convert text date to excel date format

[Cyankee]

Yes, based on the title, it is simple. I have done it numerous times. The problem I am having is this:

I copy data over as text. Copy and paste. I can then pull the date out of the text and get it into a separate cell. The problem is the date format. It is in the form of MMM DDth (or nd, rd).
Example: Date is AUG 29th. Left justified and is text. The functions "Value" and "datevalue" deliver "#value" errors.

 

[Dave Patton]

Custom format to your preference

Date and Time 3.xlsm
	A	B
1	year	2020
2	AUG 29th	Aug 29, 2020
3
1b

Cell Formulas
Range	Formula
B2	B2	=DATE(B1,MONTH(1&LEFT(A2,3)),MID(A2,5,2))

 

[Rick Rothstein]

If mmm dd, yyyy is a date format in your locale, then you can also use this formula...

=0+REPLACE(A2,7,2,", "&B1)

 

[Cyankee]

Custom format to your preference

Date and Time 3.xlsm
	A	B
1	year	2020
2	AUG 29th	Aug 29, 2020
3
1b

Cell Formulas
Range	Formula
B2	B2	=DATE(B1,MONTH(1&LEFT(A2,3)),MID(A2,5,2))

YES! That is what I needed. I bow down to your knowledge.

 

[Rick Rothstein]

Just wondering... did my formula work for you or not?