Dates and times in Microsoft Excel

At my day job I had to implement and work in a buch of importers. Initially we used CSV files but had a lot of trouble parsing data in multiple languages. In some languages the dot is used as a decimal separator. Other languages use the comma instead. Same thing happens with other formats such as currencies, dates and times.

At some point we switched to supporting Microsoft Excel instead. We did this because Excel (and similar spreadsheets) uses different formats for storing data (standard format) and presenting it (user customizable format). Two users could see a cell formatted according to their own locale, but the actual value is always the same.

I think that switching from CSV to Excel has been a good call. .xlsx files are very common and can be generated by many different softwares: Microsoft Excel, LibreOffice Calc, Apple Numbers, Google Sheets, etc.

Understanding how Excel stores certain types of data has been some times surprising, so I decided to write this as a reference for my future self and for others that find themselves in the same position.

Dates and times

As Chip Pearson writes in their fantastic Dates and Times in Excel article:

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. This is called a serial date, or serial date-time.

In most cases (if the cells are properly formatted) you will get a float when reading dates. The integer portion represents the number of days and the fractional portion represents the fractional portion of the day

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. The fractional portion of the number, ttttt, represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly, 6PM is stored at 0.75, or 75% percent of a 24 hour day.

While surprising for many, storing dates as numbers allows us to easily perform operations such as comparing, substracting or adding dates. Remember, we are just working with numbers.

Unfortunately there is a catch:

In the section above, it was said that the date portion of a serial date-time represents the number of days since 1900-Jan-0. This is true, but Excel can also treat the date portion of a serial date as the number of days since 1904-Jan-0. This mode is, called the 1904-mode or 1904-system, is used for compatibility with the Macintosh system. The date mode applies to all dates within a workbook. You cannot mix and match modes within a workbook. Unless absolutely required, you should never use the 1904-system.

The base date can be found in the Excel workbook and, while most of the times you can assume that it is 1900-Jan-0, for perfect compatibility you want to consider it when calculating dates, as some spreadsheets may use 1904-Jan-0 instead.

Numbers

Excel stores all numbers as 64-bit (8-byte) IEEE 754 floating-point values, regardless of how they’re displayed. This internal representation is independent of the formatting applied (currency, percentage, scientific notation, etc).

This is something that you must keep in mind. If you are expecting an integer you will still receive a floating-point number so you may want to round or truncate accordingly.