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]

You need to use the import wizard on the Data tab.

 

[awoohaw]

i don't know how 3CX works. it would be one formula in another column copied down after the import.
this would be the formula:

=DATE(RIGHT(B2,4),
MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1),
LEFT(B2,FIND("/",B2)-1))

 

[SueKi]

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

Thank you, thank you, thank you! I've been struggling with this same issue with QlikView, the dates not only come out wrong, but don't even come out consistently, so it's impossible to fix. Just tried this and it's spot on. Now, I have to hope it repeats in the future, but you just saved me a tone of work massaging dates manually!

 

[awoohaw]

Thank you, thank you, thank you! I've been struggling with this same issue with QlikView, the dates not only come out wrong, but don't even come out consistently, so it's impossible to fix. Just tried this and it's spot on. Now, I have to hope it repeats in the future, but you just saved me a tone of work massaging dates manually!

My pleasure. Im glad you've found a solution. Just be sure to check the date values are absolutely correct. Best wishes.

 

[fapb48]

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

importing the CSV and the all dates are formatted as general.. if i change to date it doesnt change anything.. nor does it do anything changing to number or any other format

 

[Fluff]

When you import it, on the 3rd page of the wizard select the MDY date option.

 

[fapb48]

When you import it, on the 3rd page of the wizard select the MDY date option.

Sorry if i sound stupid.. but when i select "from text/csv" and i select the file it opens power query and all i can change is file origin and delimiter

 

[fapb48]

Thank you, thank you, thank you! I've been struggling with this same issue with QlikView, the dates not only come out wrong, but don't even come out consistently, so it's impossible to fix. Just tried this and it's spot on. Now, I have to hope it repeats in the future, but you just saved me a tone of work massaging dates manually!

what have you done exactly?

 

[fapb48]

When you import it, on the 3rd page of the wizard select the MDY date option.

i have now tried with legacy import and select the MDY option but same old. Nothing changes.

 

[fapb48]

i don't know how 3CX works. it would be one formula in another column copied down after the import.
this would be the formula:

=DATE(RIGHT(B2,4),
MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1),
LEFT(B2,FIND("/",B2)-1))

i have tried this formula but it errors? does it not need the year, month and day functions?