Spreadsheet date comparisons

A comparison of dates and serial numbers across three spreadsheet applications.

  1. Microsoft Excel 2013
  2. LibreOffice Calc ver. 4.4
  3. Google sheets (ver. as at March 2015)

Dates

Three factors are reviewed here:

  • The 1900 leap year error – relates only to Excel
  • Day zero – the computer epoch
  • Negative dates – before the epoch – an error, only in Excel

Figure 1 shows the worksheet used in the spreadsheet comparison. Sheet1 is from the file named DatesSpreadsheetComparison.xlsx that has (serial date) numbers in column B. This serial date vector is linked to column C with a cell references. For example, cell C4 contains the formula =B4, with a date format applied as dd-mmm-yyyy in all cases. This file is used in all three applications.

MS Excel

xlf-dates-comparison-microsoft-excel
Fig 1: Excel 2013 – in row 10, the epoch: 00-Jan-1900, negative dates show the ######### error

Features of Excel dates:

  • The 1900 leap year error – the date 29 February 1900 did not occur, but it appears in the Excel worksheet
  • Day zero – the day before 1 January 1900, treated by Excel as 0 January 1900
  • Negative dates – before the epoch – return a ###### error. Excel does not permit serial number dates in the year 1899 or earlier

For reasons of backward compatibility, Excel retains the Lotus 123, 1900 leap year error. The date, 29 February 1900 in row 16 is incorrect as the year 1900 was not a leap year.

The error in Excel, and inconsistencies across packages during the first 61 days of the year 1900 are unlikely to effect many analysts. However, you need to be aware of the issue, and it may be of concern to those doing historical finance or other historical recording.

LibreOffice Calc

The LibreOffice interface is based on the Excel 2003 menu style.

xlf-dates-comparison-libreoffice-calc
Fig 2: LibreOffice Calc – row 10 shows the epoch: 30-Dec-1899, negative serial dates are permitted

The features of LibreOffice dates:

  • The last day of February in the year 1900 is treated correctly
  • Day zero – is 30 December 1899, meaning that day 1 is 31 December 1899. Serial days from 1 March 1900 synchronize with those in Excel
  • Negative dates – negative date serial numbers are permitted

Google sheets

The Google sheets interface is based on the Excel 2003 menu style.

xlf-dates-comparison-google-sheets
Fig 3: Google sheets – epoch: 30-Dec-1899, negative serial dates are permitted

The features of Google Sheets dates:

  • The last day of February in the year 1900 is treated correctly
  • Day zero – is 30 December 1899, meaning that day 1 is 31 December 1899. Serial days from 1 March 1900 synchronize with those in Excel
  • Negative dates – negative date serial numbers are permitted
  • LibreOffice Calc serials dates are in line with Google Sheets serial dates, albeit with different start and end points.