CODEWITHSUNDEEP
excelexcel-formula
Grantx
Grantx

Reputation: 345

Convert from US to UK format

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.

Upvotes: 0

Views: 2003

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

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.

![enter image description here

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.

With Office 365

=LET(
    z,TEXT(A1,"d/m/y")&" ",
    y,LEFT(z,FIND(" ",z)),
    x,TEXT(A1,"\ hh:mm AM/PM"),
    q,MID(x,FIND(" ",x)+1,20),
    TEXTJOIN("/",,CHOOSECOLS(TEXTSPLIT(y,"/"),2,1,3))+IFERROR(q,0))

enter image description here

Upvotes: 3

Related Questions