Convert text date including day name to DD/MM/YYYY

[Paddington1881]

Hello
Could do with some help to convert a text date to Excel-friendly date. The date has been imported from an externally produced report. The cell shows:

Wednesday January 25, 2022

I'd like it to display as "25/01/2022". I don't need the day name included.

I'm working on a combination of LEFT, RIGHT, MID, LEN and FIND but can't seem to get the correct combination. If anyone has solved the same problem before, or has any tips, I'd be very grateful.

Many thanks
Darren

 

[Joe4]

If your date was in cell J1, the formula would look like this:

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

Then just choose a format for the cell of "dd/mm/yyyy" to get it to display in the date format you want (instead of as a General number).

 

[Paddington1881]

If your date was in cell J1, the formula would look like this:

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

Then just choose a format for the cell of "dd/mm/yyyy" to get it to display in the date format you want (instead of as a General number).

I'm sorry, I just get the #VALUE! error.

 

[Joe4]

I am guessing maybe you are using a European version of Excel that uses different date structures.

Can you tell me what this formula returns?

=DATEVALUE("January 25, 2022")

Does that return a number, date, or error?

 

[Fluff]

How about

=DATEVALUE(SUBSTITUTE(MID(A8,FIND(" ",A8,FIND(" ",A8)+1)+1,2),",","")&MID(A8,FIND(" ",A8)+1,3)&RIGHT(A8,4))

@Joe4 I get a #Value error with your formula as well.

 

[Joe4]

@Joe4 I get a #Value error with your formula as well.

OK. It works over here Stateside.
The differences between dates and formats between the different versions can be a real pain sometimes!
Thanks for coming up with a solution that should (hopefully) work for the OP.

 

[Paddington1881]

@Joe4 the formula =DATEVALUE("January 25, 2022") returned #VALUE! . Thank you for your efforts.

@Fluff your formula worked - thank you very much. Not in a million year would I have been able to come up with that, but I'm going to step through it to understand how it works.

 

[Fluff]

Glad we could help & thanks for the feedback.

The differences between dates and formats between the different versions can be a real pain sometimes!

Agreed.