Formula to convert American Date to Uk Date

[Sahil123]

Hi,

What formula is best to convert an American date to Uk date?

ie 8/13/2019 to 13/08/2019
8/7/2018 to 07/08/2018

thank you

 

[Eric W]

Is the date saved as text? If the date is saved in the Excel standard, then it's just a number, and you just choose what format you want to see it displayed in. If it's text, do you want the result as text, or as an Excel date? Can you always expect a 4-digit year, or will there sometimes be 2-digits?

This should work for text values:

=SUBSTITUTE(MID(A1,FIND("/",A1)+1,2)&"/"&LEFT(A1,2)&MID(A1,FIND("/",A1,4),5),"//","/")

Depending on your regional settings, you can wrap that in DATEVALUE to get an Excel date.

 

[Sahil123]

Thank You Eric

The date was a text format like this

eg
8/7/2019-8/16/2019 (year always in 4 digits)
i managed to use some string functions to seperate them
to get 8/7/2019 and then added +0 at the end to convert into a date

So i guess the result ends up being in an excel date (the format is dd/mm/yyyy)

 

[Eric W]

Glad you got it worked out. Thanks for letting us know.