Fix Date format

Question

Fix Date format

Elyasaf Hershkovitz 0

Hi there.

I'm using windows 11.

when I download any kind of report (excel/csv) the dates are getting mixed for some reason and I cant view them under the current year/month. for example there are many rows under may 2024 and some of them will go under 2024 option and some of them will show under nothing. see image for example (not populating into 1 list).

I know that the data is right because all other users that downloading the data can see the date normally.

Any suggestions what can cause this issue?

Screenshot_1

2 answers

Your answer

Answer 1

HansV 462.3K MVP Volunteer Moderator

Do you use a system date format in day/month/year (or year/month/day) order?

It looks like the csv file contains dates in USA month/day/year order.

So you could temporarily switch to m/d/yyyy as system date format, open the CSV file, save it as an Excel workbook, then switch back to your original system date format.

Alternatively, Power Query can probably convert the dates for you.

Jiajing Hua 18,140 Reputation points Moderator

2024-07-29T07:42:28.53+00:00

Hi @Elyasaf Hershkovitz

Please refer to HansV's suggestion, search "Control Panel" and open it, locate to Clock and Region > Region, check your system date format. If the date is not in "m/d/yyyy", please modify the date format.
Elyasaf Hershkovitz 0 Reputation points

2024-07-29T10:47:23.3733333+00:00

Hi,

Thanks for the answers.

I followed your steps and I see the format like you said - in "m/d/yyyy".

Its really weird since I know that the original file is correct - other person that downloaded the file see it as expected.

In addition, when I look at the file the date are different some of the has 2 numbers format with zero at start and some without (see image for example) and when trying to filter the dates are braked, moreover when I send the file to my collogues he sees

the date correctly and not how I see it...
Elyasaf Hershkovitz 0 Reputation points

2024-07-29T11:49:10.1266667+00:00

One more thing that I have noticed that might help.

It looks like half of the rows are 'General' type and the other half are 'Date' type.

I was playing with the 'm/d/yyyy' setting and I noticed that its effect only the 'date' type (like it should happen) but I'm wondering why the rows was spited to different types?

I tried to change the column to be only in 'date' type but it didn't help.
HansV 462.3K Reputation points MVP Volunteer Moderator

2024-07-29T13:43:40.88+00:00

Try setting the date format to dd/MM/yyyy temporarily. Then open the .csv file and save it as .xlsx.
Elyasaf Hershkovitz 0 Reputation points

2024-07-29T13:50:09.4133333+00:00

I dont have this option -
HansV 462.3K Reputation points MVP Volunteer Moderator

2024-07-29T14:49:24.9266667+00:00

Click 'Additional settings...' and activate the Date tab. You can type a custom format in the 'Short date' box.
Elyasaf Hershkovitz 0 Reputation points

2024-07-29T15:14:31.3533333+00:00

Looks good, that seems to be working now.

Thanks a lot!

Have a great day....
Jiajing Hua 18,140 Reputation points Moderator

2024-07-30T01:39:57.2133333+00:00

Hi @Elyasaf Hershkovitz

I am glad that your issue went away.

Since HansV's replies are helpful to you, could you please mark this HansV's answer as "Accept Answer"?Thanks for your understanding.

Answer 2

Ashish Mathur 100.8K Volunteer Moderator

Hi,

See if my suggestion here works - https://learn.microsoft.com/en-us/answers/questions/1700240/data-from-a-csv-file-to-excel-date-is-in-mm-dd-yyy

Share via

Fix Date format

2 answers

Your answer