Forum Discussion
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...
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.
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!
John2064 wrote:
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)
Both assertions are incorrect. I suspect you are opening the CSV file (directly?) with Excel.
Instead, open the CSV with Notepad to see how the data is written to the file.
-----
John2064wrote:
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.
That is correct, but only because you chose a Date format. In that case, Excel interpets the file data just as if we had typed them into a cell formatted as General.
Instead, choose the Text format for the column in order to preserve the appearance in the CSV file.
However, beware that that might have undesirable consequences.
So, if you want the data to remain as numeric, the only work-around is to re-assert the desired format after importing the file.
"It is what it is".
I'm having the same issue trying to save a date in this format: yyyy-mm-ddd hh:mm:ss in csv. I've tried all of the solutions/options mentioned in the previous threads, and excel reverts back to the old mm/dd/yyyy format after saving/reopening. This the the format required of Turbtax and I have hundreds of crypto transactions.
scondrill wrote: ``trying to save a date in this format: yyyy-mm-ddd hh:mm:ss in csv [....] excel reverts back to the old mm/dd/yyyy format after saving/reopening``
Again, the data is written into the CSV file in that form, if that is how the data was formatted (or it is text) when the CSV is saved.
Confirm by opening the CSV file in Notepad.
However, if we open the CSV file directly with Excel, Excel interpets the file data just as if we had typed them into a cell formatted as General.
To retain that original form, do not open the file directly with Excel.
Instead, import the CSV file using Data > Get From Text, and in Step 3, select Text for the column format.
However, that might have undesirable consequences.
If the imported data must be numeric, the only work-around is to re-assert the desired format after importing the data.
