Need date formula to convert mm-dd-yyyy or mm/dd/yyyy to dd-mm-yyyy in 1 formula

[ChristopherH]

Hi guys

I have a column with dates in format mm-dd-yyyy and mm/dd/yyyy. I need a formula that converts either of them to dd-mm-yyyy.

Right now I use =DATE(RIGHT(A4;4);LEFT(A4;2);MID(A4;4;2)) which works for mm/dd/yyyy but doesnt work for mm-dd-yyyy.

Can anyone help?

Thanks

[Norie]

You could do it without a formula by selecting the column, going to Data>Text to columns... and selecting MDY for the column data format on the 3rd step.

[Kaper]

in the first case you already have a date in the cell. To deal with such situation try:
=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,3),LEFT(A2,2),MID(A2,4,2)))

[ChristopherH]

in the first case you already have a date in the cell. To deal with such situation try:
=IF(ISNUMBER(A2),A2,DATE(RIGHT(A2,3),LEFT(A2,2),MID(A2,4,2)))

Hi again Kaper

It works for some cells... however, with the cell 05/17/2012 it returns 17-05-1912 with your formula. are you able to fix that?

Cheers

Chris

[ChristopherH]

Hi again Kaper

It works for some cells... however, with the cell 05/17/2012 it returns 17-05-1912 with your formula. are you able to fix that?

Cheers

Chris

ie. this formula now has problems with dates mm/dd/yyyy

[ChristopherH]

You could do it without a formula by selecting the column, going to Data>Text to columns... and selecting MDY for the column data format on the 3rd step.

I'm not quite sure how this works. I can't get it to work.

EDIT: I think it works now, however, I didnt choose MDY. thanks alot

[Norie]

You should need to chose the format the dates are currently in, which is MDY.

If you had to choose something else perhaps it's a locale setting.

[ChristopherH]

Okay. Now with the convert to text and the formula =DATE(RIGHT(A1;4);LEFT(A1;2);MID(A1;4;2)) it works for most of the dates. However, with some of them it shows an error, I have no idea why? I'll upload example, its very strange.

EDIT: solved it, its because it says 3/25/2013 and not 03/25/2013

[Norie]

The dates have only have 1 digit for the month.