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.

 

[barry houdini]

If "date" is in A1 try this formula in B1

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

format as date

 

[robinoc]

Barry,

The formula works.

Thank you very much!

 

[Norie]

Select the column, goto Data>Text to columns..., on the 3rd step choose Date as Column data format and DMY from the dropdown.

Or use Barry's formula.

 

[eurodjuro]

barry,

I read your post on how to covert UK/european to US date formats in excel using the following formula

If "date" is in A1 try this formula in B1

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

format as date

I need to change dates the other way round from US to UK date formats......is the above formula still the same or does it need changing? If so your assistance will be most appreciated in this matter

I have around 3000+ entries that I need to change to make the information useful for me

I look forward to receiving your reply

Regards

eurodjuro

 

[Norie]

eurodjuro

What format are your dates actually in?

See my post regarding Data>Text to columns...

 

[barry houdini]

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

 

[eurodjuro]

Chaps,

Thanks for the replies......I tried the text to column trick but had no impact at all on the data.....even after doing that and trying to format the cells to date and type, it still made no difference. The information I have is downloaded into excel from an online US database.

For your information, I use a Mac and I dont know if it makes any difference.

Also, all dates are in the following format: mm/dd/yyyy.

Will try the formula and see how i get on with that........

regards

eurodjuro

 

[eurodjuro]

in fact, the dates are in this formaat:

m/dd/yyyyand mm/dd/yyyy

hope that makes sensee

 

[barry houdini]

In that case the second formula I posted above should work, used in another column