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

%3CLINGO-SUB%20id%3D%22lingo-sub-2369302%22%20slang%3D%22en-US%22%3EExcel%20changes%20custom%20format%20to%20a%20date%20when%20saving%20as%20a%20csv.%20File.%20How%20to%20get%20itnto%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369302%22%20slang%3D%22en-US%22%3EExcel%20xlsx%20keeps%20converting%20a%20custom%20date%2C%20mm-dd-yyyy%20to%20the%20following%20format%20mm%2Fdd%2Fyyyy%20when%20saving%20as%20a%20csv%20file.%20How%20do%20I%20get%20it%20to%20stop%2C%20or%20how%20can%20I%20get%20this%20format%20added%20to%20the%20list%20of%20date%20formats%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2369302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2369461%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20changes%20custom%20format%20to%20a%20date%20when%20saving%20as%20a%20csv.%20File.%20How%20to%20get%20itnto%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2369461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058171%22%20target%3D%22_blank%22%3E%40Jackie64%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20CSV%20file%20is%20a%20plain%20text%20file%2C%20not%20an%20Excel%20workbook.%20It%20doesn't%20%22know%22%20about%20Excel%20formats.%3C%2FP%3E%0A%3CP%3EThe%20problem%20probably%20occurs%20when%20you%20open%20the%20CSV%20file%20in%20Excel%20again.%3C%2FP%3E%0A%3CP%3ETo%20avoid%20it%2C%20change%20the%20extension%20from%20.csv%20to%20.txt.%3C%2FP%3E%0A%3CP%3EWhen%20you%20open%20a%20.txt%20file%2C%20Excel%20displays%20a%20wizard%20in%20which%20you%20can%20specify%20how%20to%20interpret%20each%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378154%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20changes%20custom%20format%20to%20a%20date%20when%20saving%20as%20a%20csv.%20File.%20How%20to%20get%20itnto%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThe%20problem%20is%20a%20conversion%20problem%20from%20Excel%20to%20CSV%2C%20not%20the%20other%20way%20around!%20When%20converting%20a%20Text%20formatted%20Excel%20file%20to%20CSV%2C%20the%20date%20changes%20form%20a%20custom%20date%20of%20mm-dd-yyyy%20to%20mm%2Fdd%2Fyyyy%20which%20fails%20to%20upload%20in%20a%20governmental%20program%20that%20requires%20mm-dd-yyyy.%26nbsp%3B%20It%20is%20my%20belief%2C%20along%20with%20many%20others%20using%20this%20governmental%20program%20that%20Excel%20does%20not%20have%20a%20standard%20mm-dd-yyyy%20date%20format%2C%20so%2C%20even%20though%20it%20is%20formatted%20as%20text%2C%20the%20conversion%20changes%20the%20file%20as%20what%20it%20recognizes%20as%20a%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378194%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20changes%20custom%20format%20to%20a%20date%20when%20saving%20as%20a%20csv.%20File.%20How%20to%20get%20itnto%20stop%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058171%22%20target%3D%22_blank%22%3E%40Jackie64%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20afraid%20I%20cannot%20reproduce%20the%20problem.%3C%2FP%3E%0A%3CP%3EBut%20a%20possible%20workaround%20is%20to%20use%20formulas%20in%20another%20column%20of%20the%20form%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXT(...%2C%20%22mm-dd-yyyy%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESince%20that%20is%20a%20text%20value%2C%20it%20should%20be%20saved%20without%20change%20in%20the%20csv%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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?
5 Replies

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

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

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

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

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