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

robinoc

New Member
Joined
Jan 21, 2006
Messages
8
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
If "date" is in A1 try this formula in B1

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

format as date
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
eurodjuro

What format are your dates actually in?

See my post regarding Data>Text to columns...
 
Upvote 0
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 :cool:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top