Recognizing output as date

[aprabhat]

I'm getting outputs from an auto-generating report in the following format. Excel recognizes it as "General" and I'm struggling to convert it to a recognizable date format. Don't care about the time stamp (MM/DD/YYYY would work). Any help is really appreciated!

Mar 01 2020 00:00:00

Apr 19 2020 00:00:00

Apr 25 2020 00:00:00

 

[Dave Patton]

The first option may suffice.
N.B. If your system uses USA settings, you will have to reverse parts of C2:C3, my system's regional settings are dd-mmm-yy.

T202007b.xlsm
	A	B	C
1		Option 1	Option 2
2	Mar 01 2020 00:00:00	01-Mar-2020	01-Mar-2020
3	Apr 19 2020 00:00:00	19-Apr-2020	19-Apr-2020
4
3a

Cell Formulas
Range	Formula
B2:B3	B2	=DATE(MID(A2,8,4),MONTH(1&LEFT(A2,3)),MID(A2,5,2))
C2:C3	C2	=--(MID(A2,5,2)&LEFT(A2,3)&MID(A2,8,4))

 

[Rick Rothstein]

If your computer is set to US locale, then this formula should also work...

=0+REPLACE(A1,7,0,",")

You will have to format the cell you place this formula in with the date format of your choice though.

 

[aprabhat]

Thanks, Dave and Rick ? ! Both approaches worked. I am in the US, so going to follow Rick's formula

 

[Dave Patton]

Thanks for the feedback.
Rick's suggestion also works with my setting with dates dd-mm-yy
I had tried Substitute but I didn't try Replace.

T202007b.xlsm
	A	B	C	D
1		Option 1	Option 2	Rick's
2	Mar 01 2020 00:00:00	01-Mar-2020	01-Mar-2020	01-Mar-2020
3	Apr 19 2020 00:00:00	19-Apr-2020	19-Apr-2020	19-Apr-2020
4
3a

Cell Formulas
Range	Formula
B2:B3	B2	=DATE(MID(A2,8,4),MONTH(1&LEFT(A2,3)),MID(A2,5,2))
C2:C3	C2	=--(MID(A2,5,2)&LEFT(A2,3)&MID(A2,8,4))
D2:D3	D2	=0+REPLACE(A2,7,0,",")

 

[Rick Rothstein]

Rick's suggestion also works with my setting with dates dd-mm-yy

Hmm, I could have sworn I made that same suggestion (putting the comma in after the day number for mmm dd yyyy text strings) in the past and was told it did not work for non-US locales. I guess I must be remembering that incorrectly. Thanks for letting me know it works for you also.

 

[Dave Patton]

I edited the formulas so that they would work with 1 digit or 2 digit days.

T202007b.xlsm
	A	B	C		E
1		Option 1 +	Rick's +		Option 2 +
2	Mar 01 2020 00:00:00	01-Mar-2020	01-Mar-2020		01-Mar-2020
3	Apr 19 2020	19-Apr-2020	19-Apr-2020		19-Apr-2020
4	Mar 6 2020	06-Mar-2020	06-Mar-2020		06-Mar-2020
5	Mar 17 2020	17-Mar-2020	17-Mar-2020		17-Mar-2020
3a

Cell Formulas
Range	Formula
B2:B5	B2	=DATE(MID(A2,FIND(" ",A2,5)+1,4),MONTH(1&LEFT(A2,3)),MID(A2,5,2))
C2:C5	C2	=--REPLACE(A2,FIND(" ",A2,5),0,",")
E2:E5	E2	=--(MID(A2,5,2)&LEFT(A2,3)&MID(A2,FIND(" ",A2,5)+1,4))