Convert date from dd-mm-yy to mm-dd-yy

[robinoc]

A friend sent me over a file, but the dates are in "European" format. The cell shows a date as "01.10.05" I'd like to convert it to 10/01/05.

Any ideas on how to do this?

Thanks.

 

[eurodjuro]

Barry,

Thanks for that, works perfectly

Regards

eurodjuro

 

[das_avatara]

Hi Barry,

I saw your post for convert dd-mm-yy to mm-dd-yy

=SUBSTITUTE(REPLACE(MID(A1,4,3)&A1,7,3,""),".","/")+0

Its work if the date is more then 13..I mean if the date is 29/01/09, it works to convert it to 01/29/09. But in other way, if the date is 06/01/09, its fails to convert it. End up with #Value!.

Can you help me on these issue. Looking forward for the best solutions.

Thanks

Regards,
Dasa

 

[nisar]

We can convert from dd.mm.yy to mm.dd.yy by using this formula.

if date1(dd.mm.yy) value is in A1 Cell,
then try date2(mm.dd.yy) result value in A2 Cell.

Ex:- =CONCATENATE(MID(A2,4,2),".",MID(A2,1,2),".",MID(A2,7,2))

 

[Comfy]

?

 

[nivdubai]

The formula above was for the specific case where the "dates" weren't actually dates at all but in the text format

01.10.05

If you're in the UK and have a worksheet with dates in US format then some of these will probably be recognised as dates, but the wrong date, e.g. 2nd March becomes 3rd Feb and some others, where the day is greater than 12, are not recognised as dates at all, so excel treats these as text.

This formula will convert the text entries to the correct UK date and the wrong dates to the correct UK dates, assuming the format for your original "dates" of mm/dd/yy or
mm/dd/yyyy.

=IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

If you have variable formats, e.g. with single digit months or days you could use the longer

=IF(A1="","",IF(ISTEXT(A1),(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1))&LEFT(A1,FIND("/",A1))&RIGHT(A1,2))+0,DATE(YEAR(A1),DAY(A1),MONTH(A1))))

both of these should work for both US to UK and vice versa

having said all that I think that Norie's suggestion would also work for you, using MDY as date format, and it would probably be quicker

2/13/2010 1:13:32 AM this format cannot be changed into FEB-13 by first formula, please help me

 

[nivdubai]

2/13/2010 1:13:32 AM this format I cannot change into Feb-2010 by using your first formula =IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1))) but it helped me to change this date 12/31/2013 9:45:48 AM into Dec-13, Please help me to have solution for my first issue mentioned above. <tbody> </tbody>

<tbody>
</tbody>

 

[nivdubai]

Dear Barry, Both of them are not working with this format, but other date is looking like same and accept this your both formulas.

 

[aavaav]

The formula above was for the specific case where the "dates" weren't actually dates at all but in the text format

01.10.05

If you're in the UK and have a worksheet with dates in US format then some of these will probably be recognised as dates, but the wrong date, e.g. 2nd March becomes 3rd Feb and some others, where the day is greater than 12, are not recognised as dates at all, so excel treats these as text.

This formula will convert the text entries to the correct UK date and the wrong dates to the correct UK dates, assuming the format for your original "dates" of mm/dd/yy or
mm/dd/yyyy.

=IF(ISTEXT(A1),REPLACE(MID(A1,4,3)&A1,7,3,"")+0,DATE(YEAR(A1),DAY(A1),MONTH(A1)))

If you have variable formats, e.g. with single digit months or days you could use the longer

=IF(A1="","",IF(ISTEXT(A1),(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1))&LEFT(A1,FIND("/",A1))&RIGHT(A1,2))+0,DATE(YEAR(A1),DAY(A1),MONTH(A1))))

both of these should work for both US to UK and vice versa

having said all that I think that Norie's suggestion would also work for you, using MDY as date format, and it would probably be quicker

***

This formula solved my problem. Thank you very much.

 

[Bia Aliyan]

The formulas saved my life was badly stuck in formatting