Excel: Importing csv file, date format changes from left aligned text to right aligned number if day is 12 or less. Why?

Question

The format changes from dd/mm/yyyy to mm/dd/yyyy. Cannot apply any function to change the right aligned entries to the format dd/mm/yyyy

This has only recently happened, I think since the beginning of 2023.

Using Excel 365 on one-drive, Windows 10 operating system

Date		datevalue
28/04/2023		45044
28/04/2023		45044
28/04/2023		45044
26/04/2023		45042
21/04/2023		45037
04/12/2023		#VALUE!
04/11/2023		#VALUE!
04/06/2023		#VALUE!
04/06/2023		#VALUE!
04/03/2023		#VALUE!
31/03/2023		45016
31/03/2023		45016

Answer 1

Alright, George. There is an article by Andreas pasted above. Have you tried following those?

Unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue. 

Thanks

Answer 2

Dear Anna

Thank you for your reply, unfortunately your suggestions do not solve the problem. All the date format methods have been tried but the issue is why the format changes when the days of the dates are 12 or below. In the example I showed the dates above 12 converted to a numeric value using datevalue function but those below 13 showed an error message #VALUE!. This happens because the date entries in the format dd/mm/yyyy when the day is above 12 are text, but changes to mm/dd/yyyy when day is below 13 and becomes numeric. For instance 21st April 2023 appears as 21/04/2023 but 11th April 2023 appears as 04/11/2023 even though the selected date format is dd/mm/yyyy. When I import data covering a whole month this problem presents some considerable work, all the date entries with days below 13 have to be manually converted from mm/dd to dd/mm.

As I use OneDrive for Excel operations I do not have access to Excel csv wizard, it always converts csv files without my intervention.

Thanks again for your interest, if you know of any solution or who I could contact direct in Microsoft, I would be most grateful.

Kibd Regards

George Wallace

Answer 3

Open the Windows Settings by pressing the Windows key + I on your keyboard. Select "Time & Language." In the left-hand menu, click on "Region." Under the "Regional format" section, ensure that the format is set to the desired "Short date" format (e.g., dd/mm/yyyy). If needed, you can click on "Change data formats" to customize the date format further. Click "Apply" or "OK" to save the changes.

NEVER DO THIS! This settings affect the whole PC, means all application and has unpredictable consequences!

Read this article which explains in detail how to import such a CSV file
Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.

Answer 4

Hi George

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

The issue you're facing with the date format changing when importing a CSV file in Excel can be attributed to the regional settings on your computer. You can check this following the steps below

Open the Windows Settings by pressing the Windows key + I on your keyboard. Select "Time & Language." In the left-hand menu, click on "Region." Under the "Regional format" section, ensure that the format is set to the desired "Short date" format (e.g., dd/mm/yyyy). If needed, you can click on "Change data formats" to customize the date format further. Click "Apply" or "OK" to save the changes.

However, another way to prevent this from happening is to specify the format of the data during the import process.

When you open a CSV file in Excel, You should see a Text Import Wizard. In step 3 of the wizard, you can specify the format of each column of data. For the column containing dates, you can select “Date” and then choose the appropriate format (e.g., “DMY” for dd/mm/yyyy).

If you have already imported the data and want to change the format of the dates, you can do so by selecting the cells containing the dates, right-clicking and choosing “Format Cells”, then selecting “Date” under the “Number” tab and choosing the appropriate format.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.