Date format issue in Excel need solution

Question

Hi,

When I download data from a Spotfire dashboard as CSV file its showing date in two different formats (DMY & MDY).

I am able to fix it using text to column option but I wanted to know if there is any other solution to fix it permanently. 

Screenshot attached shows what I am referring to. 

Answer 1

The date format of the CSV file does not match your locale settings, therefore you can not open the CSV file.

Have a look into this article and follow the steps to import the CSV file.
Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.

Answer 2

Vipin

I did understand the problem with i.e.:

01-08-2021 - is showing as 1st Aug 2021 when it should be 8th Jan 2021

Hence my questions you've not answered:

1. Are all MDY formatted as MM/DD/YYYY?
2. Are all DMY formatted as DD-MM-YYYY?

Answer 3

When I click on Import, Query editor opens up it doesn't give me the Load/Transform option. 

How do I get that?

Answer 4

I am from India. I don't have a preference on the format. Issue is that excel is showing some of the dates correctly and some of it incorrectly. 

For example: 

1/30/2021 - is 30th Jan 2021 - This is correct

01-08-2021 - is showing as 1st Aug 2021 when it should be 8th Jan 2021

Data is for Jan 2021 only but excel is confusing months & dates up to 12.

See below screenshot when I click on the sort option:

Answer 5

Hi

Sounds doable with Get & Transform. However, a couple of clarifications are required:

1. Are all MDY formatted as MM/DD/YYYY?
2. Are all DMY formatted as DD-MM-YYYY?
3. In which country are you? In other words do you expect the fixed dates in MM/DD/YYYY or DD/MM/YYYY format?