Dynamic Date - Converting Date Format from US to UK

[Zakky]

Hi All, Is there a way to make the formula dynamic so it works on all different formats (only works on the first date at the moment). Any help would be greatly appreciated. Thank you.


)

 

[Micron]

Did you consider just copying the values into col B and formatting that column as UK dates?

 

[Cubist]

Had a similar question today. Try this:

=--TEXTJOIN("/",1,INDEX(TEXTSPLIT(A2,"/"),{2,1,3}))

 

[Dave Patton]

a

 

[Dave Patton]

Dates and Time 2024.xlsm
	A				E	F
1					Excel 365
2	10/27/2023				27-Oct-2023	27-Oct-2023
3	2/27/2023				27-Feb-2023	27-Feb-2023
4	3/2/2023				2-Mar-2023	2-Mar-2023
5	5/07/2023				7-May-2023	7-May-2023
6	2/27/23					27-Feb-2023
7
5i

Cell Formulas
Range	Formula
E2:E5	E2	=--TEXTJOIN("-",,INDEX(TEXTSPLIT(A2,"/"),{3,1,2}))
F2:F6	F2	=LET(dt,TEXTSPLIT(A2,"/"),y,CHOOSECOLS(dt,3),--(IF(LEN(y)<4,"20"&y,y)&"-"&CHOOSECOLS(dt,1)&"-"&CHOOSECOLS(dt,2)))

 

[Dave Patton]

Use Custom format for your personal preference with the date format

Dates and Time 2024.xlsm
	A					F
1
2	10/27/2023					27/Oct/2023
3	2/27/2023					27/Feb/2023
4	3/2/2023					2/Mar/2023
5	5/07/2023					7/May/2023
6	2/27/23					27/Feb/2023
7
5i

Cell Formulas
Range	Formula
F2:F6	F2	=LET(dt,TEXTSPLIT(A2,"/"),y,INDEX(dt,3),--(IF(LEN(y)<4,"20"&y,y)&"-"&INDEX(dt,1)&"-"&INDEX(dt,2)))

 

[Zakky]

Cubist & Dave Patton. Many thanks for the prompt replies. Each solution works like a charm (does what it says on the tin). Happy days! Have a nice day.

 

[Micron]

I guess I just don't get it. I copied the US dates to the next column and formated them to UK dates and got the posted result. Why the need for complex formulas?

 

[Zakky]

Micron, How do you format them to UK dates?

 

[Alex Blakenburg]

I am surprised they worked. My guess is that rows 5 and 6 are dates / numeric and those formulas should fail on those rows.

Building on the previous suggestions and assuming your default date format is dd/mm/yyyy (UK not US) it should need something like this.

=--TEXTJOIN("/",1,INDEX(TEXTSPLIT(TEXT(A2,"dd/mm/yyyy"),"/"),{2,1,3}))

@Micron the OP's profile says US but it sounds like his default date format is UK.