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

1. Dates


Three factors are reviewed here:


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.


2. 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:


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.


3. 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:


4. 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: