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

 

[bat17]

try
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
where a1 is the date to convert

HTH

Peter

 

[Peorth]

@Peter

I am not sure that your formula is correct, I tested it an I don't always return the expected.

I did a different one, even if it looks much complex:

=IFERROR(IF(CELL("prefix",A1)="'",DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1),MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1)),"")

 

[Norie]

You could try Data>Text to Columns...
On the third step select MDY from the column format dropdown .

 

[Peorth]

The reason for that formula is that american dates are align on the right while the UK ones are aligned on the left (if no formatting has been applyed and if the dates are mixed)
I got this problem importing transaction with date filed from another software.

 

[Peorth]

Norie, that solution doesn't sort out the problem and you can get in trouble doing further calculation or things like running a Pivot Table.
The dates still remain as American.

If you are lucky that the American dates are align on the right and the UK on the left, try to test my formula

 

[doofusboy]

What about something like this? I tested with 'American' dates in A1:A5 and put the 'British' dates in column C.

Dim i As Integer
    For i = 1 To 5
        Range("C" & i).Value = Format(Range("A" & i).Value, "dd-mm-yyyy")
    Next i

 

[jeffreybrown]

Maybe...

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

 

[Peorth]

@doofusboy

good job but is not working if you have mixed dates

@jeffreybrown

your formula returns #value ...at least in my case...

anyone tested my formula?

 

[doofusboy]

mixed dates?