Forum Discussion
Excel changes custom format to a date when saving as a csv. File. How to get itnto stop?
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!
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.