US Date to UK Date Not Straight Forward

[corbie82]

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]

In Text to Columns select MDY, not DMY.

 

[corbie82]

In Text to Columns select MDY, not DMY.

Thanks lot

 

[Fluff]

You're welcome

 

[Rick Rothstein]

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.