Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert a date in Excel to ISO 8601 format

Tags:

date

csv

excel

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?

like image 522
Mike C Avatar asked Dec 09 '14 21:12

Mike C


People also ask

How do I convert a date to ISO in Excel?

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. Start out with a csv that has format 21-6-2019. Make sure your regional settings are the same.

How do I format a date in ISO 8601?

ISO 8601 represents date and time by starting with the year, followed by the month, the day, the hour, the minutes, seconds and milliseconds. For example, 2020-07-10 15:00:00.000, represents the 10th of July 2020 at 3 p.m. (in local time as there is no time zone offset specified—more on that below).

How do I change the date format in Excel?

Select the cells you want to format. Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.


2 Answers

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.

like image 111
Dirk Bester Avatar answered Oct 04 '22 02:10

Dirk Bester


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)
like image 25
RockDaFox Avatar answered Oct 04 '22 03:10

RockDaFox