How to convert a date in Excel to ISO 8601 format

Question

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.

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?

Solution 1

The basic function is:

=TEXT(A1,"yyyy-mm-ddThh:MM:ss")

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.

Solution 2

You can set up a cell, example :

Right click on cell > Cell format > Category > Custom > Type > write this :

aaaa-mm-jjThh:mm:ss (french format here)
yyyy-mm-ddThh:MM:ss (english format)

Solution 3

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:

=TEXT(C2;"jjjjMMtt")

(Although the OS and Excel are set to English.)

Solution 4

I copied and pasted @Dirk Bester's formula above:

=TEXT(A1,”yyyy-mm-ddThh:MM:ss”)

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:

=TEXT(A1,"yyyy-mm-ddThh:MM:ss")

And now it works like a charm.

Solution 5

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.

View more solutions

Comments

[Reversed Engineer]

Yes, the M/D/Y format is pretty useless - you can't sort by date on it, and violates what we learned in grade 1 - numbers have their most significant digits on the left, least significant on the right (hundred / tens / units, remember). So millennia (i.e. thousands of years) are obviously more significant than hundreds, tens, units of years, then tens of months, units of months, then tens of days, units of days. Dunno why people still use M/D/Y in the 21st century - it should be banned.

[Dirk Bester]

Weird, wonder how those slipped in there. Smart quotes are really dumb.

[keuleJ]

I think you should use =TEXT(A2;"jjjjMMtt") instead, or you show minutes instead of Months

[Tim Nevins]

Open the CSV file in notepad to test this theory. Excel is too clever for it's own good some days.

[user2190601]

Also, for milliseconds it is: yyyy-mm-ddThh:MM:ss.000

[wide_eyed_pupil]

I would prefer to use this method, to keep the time and date alive in Excel (not just a text string), but I can't work out how to add the time zone date eg: 2014-01-01T01:00:00+0800 indicating the Western Australian timezone of +08 hours relative to GMT.

[RolfBly]

Start out with a csv that has format 21-6-2019. Make sure your regional settings are the same. Doubleclick the CSV to open in Excel (do not use data/from text, that won't work. Don't ask me why). Excel will recognise the dates as dates. Now select each date column and change the format Date to 2019-06-21. Excel 2016.