date conversion

[BigK54]

won't hold in CSV format from Excel workbook. Every time I reopen the file, it converts back to mo/day/year from the year-mo-day format that I had changed it to. What Am i not doing here?

 

[Fluff]

You can save formats in a CSV file.

 

[BigK54]

You can save formats in a CSV file.

I have been, it doesn't stick.

 

[thisoldman]

I can repeat the formatting problem, no matter which csv format I use for saving the file. Saving as a txt file doesn't change things.

Excel is just trying to be helpful—it recognizes the entry as a date and displays the cell using the system 'short date format'. That is, it converts the date string from the file to an Excel date. The only way I found to work around this is to change the system short date format to yyyy-mm-dd.

Using Windows 10, you can get to the date and time settings quickly by left clicking on the clock in the system tray. Click on 'Date and time settings' at the bottom of the calendar, then click 'Change date and time formats'. Finally, make your selection under 'Short date'.

 

[Fluff]

I have been, it doesn't stick.

My apologies, a slight typo in my post it should have read

You can't save formats in as CSV file

 

[Joe4]

I find that using Custom Date Formats and saving to a CSV file DOES, in fact, keep the Custom date format.
The issue is, that many people do not actually know how to best view to CSV file.
Do NOT view it in Excel!!! Why? Excel applies a conversion on it automatically when you open the CSV file!

If you TRULY want to know what the contents of a CSV file look like, NEVER USE EXCEL to view its contents! Use a Text Editor, like NotePad, WordPad, or some other after-market Text Editor.
That will show you what is really contained in the CSV file.

This is one of my biggest pet peeves with Microsoft - that someone thought Excel should be the default program to open and view CSV files. Horrible decision!
One of the first things I do when I get a new computer is change that default to a Text Editor.

 

[thisoldman]

I agree with you Joe, I usually open them with a code editor, Vim, or, lately, in RStudio. The csv files are saved just fine by Excel.

This isn't my biggest annoyance with Microsoft, but it ranks up there with code pages for languages and their conflating UTF-16 for Unicode.

 

[BigK54]

I can repeat the formatting problem, no matter which csv format I use for saving the file. Saving as a txt file doesn't change things.

Excel is just trying to be helpful—it recognizes the entry as a date and displays the cell using the system 'short date format'. That is, it converts the date string from the file to an Excel date. The only way I found to work around this is to change the system short date format to yyyy-mm-dd.

Using Windows 10, you can get to the date and time settings quickly by left clicking on the clock in the system tray. Click on 'Date and time settings' at the bottom of the calendar, then click 'Change date and time formats'. Finally, make your selection under 'Short date'.

Thank You, that was a simple fix, I greatly appreciate it! if you come this way, I'll buy you drink! Doug