Date format changes

[fgwall3gmailcom]

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. 

[#2]

@fgwall3gmailcom 

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:

1. Open Excel and go to "File" > "Options."
2. In the Excel Options dialog box, select "Advanced" from the left sidebar.
3. Scroll down to the "When calculating this workbook" section and look for the "Use system separators" option.
4. Uncheck the "Use system separators" option and manually set the date format to "dd/mm/yyyy."
5. 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:

1. Open a new blank Excel workbook.
2. Go to "File" > "Options" > "Advanced."
3. Scroll down to the "General" section and locate the "When creating new workbooks" option.
4. Select the desired date format, such as "dd/mm/yyyy."
5. 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.

[#3]

@NikolinoDE 

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.  

[#4]

Please provide detailed information about the operating system, Excel version, storage medium and, if possible, photos of the settings in Windows and Excel.

[#5]

@fgwall3gmailcom 

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:

1. Select the column containing the dates.
2. Right-click on the selected column and choose "Format Cells" from the context menu.
3. In the Format Cells dialog box, go to the "Number" tab.
4. Choose "Custom" from the Category list.
5. In the "Type" field, enter the desired date format. For example, enter "dd/mm/yyyy" or "dd-mm-yyyy".
6. 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.

[#6]

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 ?

[#7]

@fgwall3gmailcom 

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.