Forum Discussion
Date format changes
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.
This did not work for me. I didn't find the settings you were referencing. However, this worked.
On Windows:
Go to Control Panel > Region > Additional Settings > Date.Change Short date format to MM/dd/yyyy.
Click OK and restart Excel.
This affects all date formatting across your system, so use with caution
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.
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.
If you use Excel for Mac may be the options slightly different.
Here is an alternative method you can try to resolve the date format issue:
- Select the column containing the dates.
- Right-click on the selected column and choose "Format Cells" from the context menu.
- In the Format Cells dialog box, go to the "Number" tab.
- Choose "Custom" from the Category list.
- In the "Type" field, enter the desired date format. For example, enter "dd/mm/yyyy" or "dd-mm-yyyy".
- Click "OK" to apply the format to the selected column.
By setting the custom date format directly on the column, you can ensure that the dates are displayed correctly as dd/mm/yyyy.
If the issue persists, it is possible that the format is being changed during the CSV conversion process. In that case, you can try modifying the CSV file itself before importing it into Excel. Open the CSV file in a text editor (such as Notepad) and enclose the date values within double quotation marks (e.g., "01/01/2023"). Save the modified CSV file and then import it into Excel. This can help preserve the desired date format during the import process.
If you are still encountering difficulties with the date format, it may be worth reaching out to Microsoft support for further assistance, as they can provide more specific guidance for Excel on Mac and help troubleshoot the issue.
- Sorry I thought I had replied but it does not show in the thread so presumably it never arrived,
My computer is a HP Pavilion Core I5 laptop with Windows 10 and uses One Drive Excel program.
As I mentioned earlier I have tried all format variants to resolve the problem without any success. What the problem is that dates with day value greater than 12 are text and below 13 are number when converted from csv to Excel. Your suggestion to change the csv file date values with double quotes did not work.
Thanks again for your help, could you let me know how Microsoft Support can be contacted ?
- Please provide detailed information about the operating system, Excel version, storage medium and, if possible, photos of the settings in Windows and Excel.
