Date from text to excel date format

[freddie_1]

I have a date in the following format:

Aug 27, 2021 00:00

Aug 26, 2021 00:00

I've tried applying the following formula to get an excel format date but no cigar. I get a #VALUE! error in the cell. I've tried formatting the cell above to a text format and the date cell to date format but makes no difference.

=DATE(MID(A1,9,4),LEFT(A1,3),MID(A1,5,2))

Any help would be appreciated. Ideally I'd just like the date value to be in the format DD/MM/YYYY.

 

[Fluff]

How about

=DATE(MID(A2,9,4),MONTH(LEFT(A2,3)&"1"),MID(A2,5,2))

 

[steve the fish]

This may work:

=0+SUBSTITUTE(MID(A1,5,99),",",LEFT(A1,3))

 

[freddie_1]

Aha.....thank you...both work great !!!

 

[Fluff]

Glad we could help & thanks for the feedback.