Forum Discussion
fgwall3gmailcom
Jun 08, 2023Copper Contributor
Date format changes
Date format changes from dd/mm/yyyy to mm/dd/yyyy when using csv conversion for day less than 13. Seems input changes from text to number when day is less than 13. This is a recent fault, within the last three months, previously all dates downloaded as text and easily converted to format dd/mm/yyyy .
I have found csv file converts without any problems on an older edition of Excel which provides a file to import into latest Excel edition, needed for its updated functions and applications.
Hope you can help to solve this strange error.
- NikolinoDEGold Contributor
If the date format changes when using CSV conversion and the day is less than 13, it could be due to the regional settings or default date format settings in your Excel application.
Here is how you can try to fix this problem:
- Open Excel and go to "File" > "Options."
- In the Excel Options dialog box, select "Advanced" from the left sidebar.
- Scroll down to the "When calculating this workbook" section and look for the "Use system separators" option.
- Uncheck the "Use system separators" option and manually set the date format to "dd/mm/yyyy."
- Click "OK" to save the changes.
By disabling the "Use system separators" option and manually setting the date format, you can ensure that Excel interprets the dates correctly in the desired format.
If the above steps do not resolve the issue, you can try the following additional steps:
- Open a new blank Excel workbook.
- Go to "File" > "Options" > "Advanced."
- Scroll down to the "General" section and locate the "When creating new workbooks" option.
- Select the desired date format, such as "dd/mm/yyyy."
- Click "OK" to save the changes.
By setting the default date format for new workbooks, you can ensure that any new files you create will use the correct date format.
If the problem persists, it's possible that there might be a compatibility issue with the specific version of Excel you're using. In that case, you may want to consider updating your Excel version to the latest release or contacting Microsoft support for further assistance.
- fgwall3gmailcomCopper Contributor
Thank you for your response, I'm afraid all I get when I choose "File">"Option" is "Regional Format Settings" , there are no other options or side bars. When selected a new dialogue box opens showing "English (United Kingdom)" all it does is show an international list of other regional settings.
I have tried using under "Number" the "General" section selecting the date format dd/mm/yyyy and even using "Custom" and then "Date" to set the same format to no avail. There is no problem with the wanted date format for dates with days larger than 12, its when the day is below 13 the format changes unexpectedly.
- nigenaCopper Contributor
fgwall3gmailcomwhen you say that you only have this problem when the date number is larger than 12, is because you have the issue always, is just that you don't notice because the excel is identifying what you write inverting the month with the day, so if you put, for example, 01/12/1990 (january first) the excel think you are writing 12 of december. I have the same problem, that could be "solved" changing the regional settings but I don't want to do that because my keyboard is for US language and if I change the regional setting, my keys won't work correctly. I would only like to change the date configuration, if possible.