Dates<\/h2>\n
Three factors are reviewed here:<\/p>\n
- \n
- The 1900 leap year error – relates only to Excel<\/li>\n
- Day zero – the computer epoch<\/li>\n
- Negative dates – before the epoch – an error, only in Excel<\/li>\n<\/ul>\n
Figure 1 shows the worksheet used in the spreadsheet comparison. Sheet1 is from the file named DatesSpreadsheetComparison.xlsx<\/em> 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<\/code>, with a date format applied as dd-mmm-yyyy<\/strong> in all cases. This file is used in all three applications.<\/p>\nMS Excel<\/h2>\n
![\"xlf-dates-comparison-microsoft-excel\"](\"..\/online\/wp-content\/uploads\/2015\/02\/xlf-dates-comparison-microsoft-excel.png\")
<\/a>Fig 1: Excel 2013<\/strong> – in row 10, the epoch: 00-Jan-1900, negative dates show the ######### error<\/figcaption><\/figure>\n <\/p>\n
Features of Excel dates:<\/p>\n
- \n
- The 1900 leap year error – the date 29 February 1900 did not occur, but it appears in the Excel worksheet<\/li>\n
- Day zero – the day before 1 January 1900, treated by Excel as 0 January 1900<\/li>\n
- Negative dates – before the epoch – return a ###### error. Excel does not permit serial number dates in the year 1899 or earlier<\/li>\n<\/ul>\n
For reasons of backward compatibility, Excel retains the Lotus 123, 1900 leap year error<\/a>. The date, 29 February 1900 in row 16 is incorrect as the year 1900 was not a leap year.<\/p>\n
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.<\/p>\n
LibreOffice Calc<\/h2>\n
The LibreOffice interface is based on the Excel 2003 menu style.<\/p>\n
![\"xlf-dates-comparison-libreoffice-calc\"](\"..\/online\/wp-content\/uploads\/2015\/02\/xlf-dates-comparison-libreoffice-calc.png\")
<\/a>Fig 2: LibreOffice Calc <\/strong> – row 10 shows the epoch: 30-Dec-1899, negative serial dates are permitted<\/figcaption><\/figure>\n <\/p>\n
The features of LibreOffice dates:<\/p>\n
- \n
- The last day of February in the year 1900 is treated correctly<\/li>\n
- 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<\/li>\n
- Negative dates – negative date serial numbers are permitted<\/li>\n<\/ul>\n
Google sheets<\/h2>\n