Library Guides: Escape from Spreadsheet Hell: Dates and times

Date and time values

An international standard

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).

Date

Y = year, M = month, D = day

YYYY-MM-DD or YYYYMMDD

Example: 2019-05-25 or 20190525 is May 25, 2019.

Time

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.

Beware of auto-formatting

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:

Setting the day of the week for a specific date (i.e. DATE is a Monday),
Calculating the difference between two times (DATE&TIME1 - DATE&TIME2 = X).

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"?

Fig.1 Excel can display the date for Sunday, March 8, 2005 in a number of ways but it always stores it as '38480.' In the last cell a time (2:15:16 PM) has been added to demonstrate how Excel stores date specific times as number values.

A work-around

To work around date and time auto-formatting the following is recommended:

Don't store date and time in the same cell (i.e. avoid using one-cell time stamps) .
Use the ISO 8601 for entering and formatting dates and times.
- Set columns/cell formatting before entering data.
- or, store date and time values as text (this prevents auto-formatting).
Store day, month, and year values in their own columns.

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: