Forum Discussion
Date format changes
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.
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 ?After researching on the internet, it seems to be a current issue that has been appearing in the last 3 months.
Saving to CSV in Excel loses regional date format
Excel changes custom format to a date when as a csv. File. How to get itnto stop?
I can't help more about that, I'm at my wit's end.