US Date to UK Date Not Straight Forward

corbie82

New Member
Joined
Jun 7, 2014
Messages
23
I have a report where half the date is american and uk.
I cant change the us dates to uk .

Tried using DMY in Text Column
Tried right clicking and changing the format cells to the right format.
Tried copying the column and repeating all of the above
Tried copying the data into notepad and then copying out .

Any Ideas
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,396
Office Version
365
Platform
Windows
In Text to Columns select MDY, not DMY.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,396
Office Version
365
Platform
Windows
You're welcome
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
I have a report where half the date is american and uk.
For this response, I am assuming you live in the UK or, at least, a locale that using UK date formats...

I am a little concerned by the above quoted statement by you. How do you know that half the dates are UK dates? Because they go into your worksheet as real dates? If so, that is not proof they are UK dates. My suspicion is that all of your dates are American dates and you are being fooled by the ones Excel recognizes as a real date. Here is the problem. A date like 7/23/2019 (American format) will not become a real date when place into your (assumed UK locale) Excel worksheet because Excel will not recognize 23 as a proper month number. However, a date like 7/5/2019 (American format) will look like a real date to Excel and it will be placed into your worksheet, but NOT as July 5, 2019 (the actual American date), but rather Excel will put it into your worksheet as May 7, 2019 (the UK date it thinks it sees). So I suspect that none of the dates you see as real dates on your worksheet are correct (unless, of course, the month number and day number equal each other). How are you getting these dates into your worksheet... describe the process you are using.
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top