Convert American dates to British dates

[Cara]

Is it possible to use a formula or cell formatting in Excel to convert a column of American dates mm/dd/yyyy to British formats dd/mm/yyyyy.
I have tried the conventional custom formatting but it doesn't modify the existing dates
Thanks

 

[Dabby]

If you put a ' in front of it to turn it to text then you get 05 April 2020 not 05 April 2018 (you have a 2 when it should be a 4).

Agreed that in my post 38

note final formula digit 2 for yy will be 4 if yyyy.

and of course C1 cell

[LEFT][COLOR=#333333][FONT=Verdana]04/05/18 HARRODS LONDON PURCHASE £6.76[/FONT][/COLOR][/LEFT]

is yy so 2 is ok.
Thanks Mark for advising code tags should be used around formulae. Still finding my way around!
I do get #VALUE! warnings when building code. Very useful to let me know formula isn't yet right!
VBA testing I dare not venture into. I just stick at it until I see each formula is working and then cross check date value such as 43224 for UK 4-May-2018 is correct and

=TEXT(B1,"ddd")

gives Friday.
My formula samples will only work in the US date environment so you may not be able to test them out!
I'm still puzzled why the same Kingsoft 2012 free spreadsheet reverses the mm/dd to dd/mm when I revert to Windows 7.
​

Thanks Mark and Spiller.

 

[MARK858]

As the site has stated their software only reverses the appearance and it doesn't correct the underlying value.
Btw, I haven't advised to use code/quote tags around formula, I do use them because I feel they make it more presentable/easier to read but my previous posts used Excel Jeanie.

 

[Dabby]

Easy to become confused by Excel dates not being what they appear to be!
I like to use =TEXT(B1,"ddd") in cell A1. Early warning if the wrong weekday is given!
Also entering = in a spare cell and clicking on the B1 date cell should give the five
digit underlying value (say 43224) which will not accord with the date in B1 if the dates
are playing tricks!

 

[MARK858]

The givaway is the formula is TEXT so there is no underlying number, it is text.