Excel thinking the date is in UK format but its actually in US. no way to convert it

[fapb48]

Hi everyone,

I have an export file with a field of "call time" that contains date that is in US format mm/dd/yyyy

But for whatever reason excel thinks this is UK date format and no way to convert this field to a proper date?

Screenshots below:

Converting text to columns and select DMY will not make any changes at all

 

[Fluff]

Converting text to columns and select DMY will not make any changes at all

You need to use MDY as that is the original format.

 

[fapb48]

You need to use MDY as that is the original format.

Still doesnt change anything. Excel still thinks its 3rd May instead of 5th of March

 

[Fluff]

How are you getting the data?

 

[fapb48]

How are you getting the data?

its an export from 3CX. i have tried a CSV export and HTML export (Copy to XLSX empty sheet) and the behaviour is identical for both formats

 

[Fluff]

How are you importing the csv into Excel?

 

[fapb48]

How are you importing the csv into Excel?

Just opening the downloaded file with Excel

 

[awoohaw]

Can you import that date as text, and then convert it manually? I know that is a pain, but it may work.

 

[Fluff]

In that case rather than opening the file in xl, try importing it using the Text import Wizard & set the date format there.

 

[fapb48]

Can you import that date as text, and then convert it manually? I know that is a pain, but it may work.

It’s 3 thousand rows the report.. how would i import the date as text if the export captured from 3CX sets the column as custom?