Forum Discussion
Jackie64
May 19, 2021Copper Contributor
Excel changes custom format to a date when saving as a csv. File. How to get itnto stop?
Excel xlsx keeps converting a custom date, mm-dd-yyyy to the following format mm/dd/yyyy when saving as a csv file. How do I get it to stop, or how can I get this format added to the list of date form...
HansVogelaar
May 19, 2021MVP
A CSV file is a plain text file, not an Excel workbook. It doesn't "know" about Excel formats.
The problem probably occurs when you open the CSV file in Excel again.
To avoid it, change the extension from .csv to .txt.
When you open a .txt file, Excel displays a wizard in which you can specify how to interpret each column.
- Jackie64May 22, 2021Copper Contributor
HansVogelaar The problem is a conversion problem from Excel to CSV, not the other way around! When converting a Text formatted Excel file to CSV, the date changes form a custom date of mm-dd-yyyy to mm/dd/yyyy which fails to upload in a governmental program that requires mm-dd-yyyy. It is my belief, along with many others using this governmental program that Excel does not have a standard mm-dd-yyyy date format, so, even though it is formatted as text, the conversion changes the file as what it recognizes as a date.
- JoeUser2004May 26, 2021Bronze Contributor
Jackie64 wrote: ``The problem is a conversion problem from Excel to CSV, not the other way around!``
As Hans explained, that is not correct. But the "debate" can be resolved very simply.
Jackie, please attach the __original__ Excel file and the __saved__ CSV file.
Of course, the "original" Excel file does not have to contain any proprietary information. Simply create an __example__ Excel file that demonstrates the problem.
According to your description, it should require only one cell with a value displayed in the form mm-dd-yyyy.
Contrary to what Hans implies, it does not matter if the data is text or a numeric date. All that should matter is how the date appears in Excel before saving as CSV.
-----
Also, please clarify....
You wrote: the CSV file ``fails to upload in a governmental program that requires mm-dd-yyyy. It is my belief, along with many others using this governmental program that Excel does not have a standard mm-dd-yyyy date format``.
If another application ("a government program") is reading the CSV file, what difference does it make that Excel does not have a standard date format?
Is the "government program" an Excel file, or does the government application otherwise rely on Excel to open / read / interpret the CSV file?
This answer might be critical in determining your options for working around the problem.
Obviously, we cannot change the way a third-party program opens / reads / interprets the CSV file.
- John2064Mar 02, 2023Copper Contributor
Similar issue here:
I got mixed Data format from original source on date, original data mix like
01/31/2023 (General) 44928 (General) 1. Use Excel format function, 44928 can be converted to MM/DD/YYY Date format, but when save file into CSV, format becomes M/DD/YYYY.
2, using = Text( original cell , "MM/DD/YYYY") , when save file into CSV file, format becomes M/DD/YYYY failed (instead of MM)
3. Open Source file via Excel, Data/ From Text/CSV, File opened with correct Date format , when save to CSV file, format becomes M/DD/YYYY failed again.
I guess the actual question/root cause is how to covert 44928(under Text format) into date format permanently!
- HansVogelaarMay 22, 2021MVP
I'm afraid I cannot reproduce the problem.
But a possible workaround is to use formulas in another column of the form
=TEXT(..., "mm-dd-yyyy")
Since that is a text value, it should be saved without change in the csv file.
- Jackie64May 26, 2021Copper ContributorHaha that is exactly what I can't have!!!!! It must be mm-dd-yyyy. It is required to be this format, no other!!!!