ISO 8601 establishes an international standard for how dates and times are formatted. Using this format guarantees data interoperability across programs. The basic formats for date and time are shown below and you can reference Wikipedia for more advanced formats (such as milliseconds).
Y = year, M = month, D = day
YYYY-MM-DD or YYYYMMDD
Example: 2019-05-25 or 20190525 is May 25, 2019.
h = hour, m = minute, s = second. This standard also assumes the use of the 24-hour clock system.
hh:mm:ss or hhmmss
Example: 14:05:30 or 140530 is read as 2:05 PM (14:05) and 30 seconds.
By default, Excel and many other spreadsheet programs auto-format dates and times into number strings so they can perform time-based calculations such as:
This conversion is both convenient and dangerous as it can lead to data loss or read errors when opening data in other programs. In the example below (Fig 1) you can see that the date Sunday, May 8, 2005 is stored as '38480' by Excel. This value was determined by counting how many days have passed since Monday, January 1, 1900. Time is also calculated using midnight of Jan 1, 1900 as a starting point.
This conversion may be useful for Excel but not for the rest of us, so how do you get around this "feature"?
To work around date and time auto-formatting the following is recommended:
Date and time data following these recommendations should export correctly. Additionally the following formats will convert to ISO 8601 format when an Excel spreadsheet is exported to .csv:
Megan O'Donnell
mno@iastate.edu
Data Services Librarian
ENT, EEOB, NREM, and Environment.
Kris Stacy-Bates
kksb@iastate.edu
Science and Technology Librarian
ABE, CCEE, Math, and Stats
Katie Wampole
kwampole@iastate.edu
Research Data Curator