Excel changes custom format to a date when saving as a csv. File. How to get itnto stop?

[Jackie64]

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 formats?

[#2]

@Jackie64 

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.

[#3]

@Hans Vogelaar 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.

[#4]

@Jackie64 

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.

[#5]

Haha that is exactly what I can't have!!!!! It must be mm-dd-yyyy. It is required to be this format, no other!!!!

[#6]

@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.

[#7]

@Joe User 

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!

[#8]

@John2064 

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. 

[#9]

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

[#10]

@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.

[#11]

I was running into this same issue. We needed the date field to be mm/dd/yyyy and when saving as "CSV (Comma delimited) (*.csv)" the date field would go back to m/d/yyyy which is not an acceptable value for the system I was uploading it into (Cornerstone CSX). Well, it turns out that it might have been seeing it as a special character when Excel saved. I changed the file type option to "CSV UTF-8 (Comma delimited) (*.csv)" and the date remained in the format I needed and uploaded perfectly into the system I was needing it for. So, people on the thread if you haven't found a solution give that a try.