Active9 months ago
Excel Iso 8601 Date Format
I am trying to save a date format in YYYY-MM-DD, for example, 2014-09-01 as a CSV file, but the format reverts back to the M/D/YYYY format when I do.
Sep 04, 2009 The Elsmar Cove Business Systems and Standards Discussion Forums > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel.xls Spreadsheet Templates and Tools: ISO 9001 Audit Checklist Excel.xls Template.
I tried converting the date as a string in Excel, but every time I open up the CSV file, it's back to the M/D/YYYY format. I need the ISO 8601 date format to be saved in a CSV file. How do I go about doing so?
Peter Mortensen14.4k1919 gold badges8888 silver badges117117 bronze badges
Mike CMike C
7 Answers
The basic function is:
Use this to convert your Excel date columns to separate ISO 8601 date columns. Next, copy the ISO 8601 columns onto the originals (paste special: paste values only).Delete the calculated ISO 8601 columns which now have garbage in them because Excel sucks at ISO 8601 dates.
You now have a transformed CSV or TSV or whatnot. Just save as the original format and ignore the stupid Excel whining about it not being its native file format and how you are going to 'lose out' somehow by saving as CSV file and try not to think about the hours of your life Microsoft has stolen with that dumb dialog.
Peter Mortensen14.4k1919 gold badges8888 silver badges117117 bronze badges
Dirk BesterDirk Bester
As a note of caution for non-English users. It just took me a while to figure out, that the format string is sensitive to your regional settings / locale. E.g. with my formatting settings to German:
(Although the OS and Excel are set to English.)
Martin FaustMartin Faust
I copied and pasted @Dirk Bester's formula above,
but it wouldn't work, Excel 2010 complaining high and low -- that is, until I changed the quote marks from some kind of 'smart quote' to plain old ASCII 0x22 quote marks:
And now it works like a charm. FYI.
Peter Mortensen14.4k1919 gold badges8888 silver badges117117 bronze badges
Tom HundtTom Hundt
You could just jump straight to the nuclear option: Change your computer’s 'Region and Language' settings to use the 'yyyy-MM-dd' short date format.
Jeffrey L WhitledgeJeffrey L Whitledge48.2k88 gold badges5858 silver badges9292 bronze badges
You can set up a cell, example :
Right click on cell > Cell format > Category > Custom > Type > write this :
Sorry for my english.
RockDaFoxRockDaFox
I believe you may well have created the right format in the .csv file.
But that Excel is automatically coercing that into a date value of the format you mention when you open the .csv file. A solution may be to import the .csv file rather than open it, and at step 3 of Get External Data, From Text, ensure that Text is selected for Column data format, where appropriate.
Excel Iso 9001
Peter Mortensen14.4k1919 gold badges8888 silver badges117117 bronze badges
pnutspnuts50.9k77 gold badges6666 silver badges105105 bronze badges
Iso 9001 Pdf
FWIW, none of the above worked for me in an Excel with 16th century dates, e.g. 26-08-1558 for the 26th of August 1558. So in order to convert that to an ISO date, I used:
RolfBlyRolfBly1,37533 gold badges1818 silver badges3434 bronze badges