excel - Convert from US to UK format

Question

How do I modify the following to be D/M/YYYY?

8/7/20 6:20 PM

8/17/20 4:24 PM

Currently it is M/D/YY

Here is what I have done to no avail:

1. =VALUE(LEFT(A2,FIND(" ",A2,1)-1)) - This is to extract the date portion (because excel sees it as text)
2. Tried to convert the date to a D/M/Y using 'text to columns' but that does not work. Excel sees the middle number as the date so anything over 12 and it throws an error.

Thank for any help on this. It's breaking me.

Answer 1

use:

=TEXTJOIN("/",,FILTERXML("<a><b>"&SUBSTITUTE(LEFT(TEXT(A1,"d/m/yy")&" ",FIND(" ",TEXT(A1,"d/m/yy")&" ")-1),"/","</b><b>")&"</b></a>","//b["&{2,1,3}&"]"))+MID(TEXT(A1," hh:mm AM/PM"),FIND(" ",TEXT(A1," hh:mm AM/PM"))+1,99)

and then format the output as desired.

Note: I am US based so I had to reverse it in the demo.

Another Note: If one is US based dealing with UK dates then change the d/m/yy to m/d/yy and it will work.