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!
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".
