Excel supports two date systems, the 1900 and 1904 date system, which can pose potential problems in financial modelling. The 1900 date system is synonymous with Microsoft Excel for Windows, whilst Excel for Mac © (Apple) applies the 1904 date system.
This financial model is demonstrated in the below video on dealing with the two Excel date systems.
Strategize FM – Dates from Excel for Mac to MS Excel
The Difference
The 1900 date system is represented by 1st January 1900 as its first day, thus a date entered into a Microsoft Excel © financial model will be converted into a serial number, which denotes the number of elapsed days after 1st January 1900. Hence, a capital expenditure for an office building for $5,000,000 accounted for on 20th January 2014, will be designated with a serial number of 41659.
On the other hand, the 1904 date system has its first day on 1st January 1904. A date entered into an Excel for Mac © spreadsheet will be translated into a serial number, which is the quantity of days after 1st January 1904. Let’s again illustrate the above $5 million example. If entered into an Excel for Mac © financial model for the date of 20th January 2014; this transaction will be assigned a date serial number of 40197.
The Problem
It is unlikely financial modellers will be modelling dates before 1st January 1904, which Excel for Mac © is unable to support because of the early design of Macintosh computers. The problem in a financial model is how the same date, i.e. 20th January 2014, can contain two different serial numbers:
- 1900 date system: 41659
- 1904 date system: 40197
A financial model in Microsoft Excel © sourcing financial information from an Excel for Mac ©-sourced spreadsheet, could face problems with this date serial number phenomena.
As the above video demonstrates, capital expenditure transactions recorded in an Excel for Mac © (1904 date system) financial model, and imported into an Microsoft Excel © (1900 date system) will appear as dates that are 1,462 days earlier – the difference between 1st January 1900 and 1st January 1904.
For example the $5 million office building capex will be dated 19th January 2010.
In other words, a difference of 4 years and 1 day.
Correcting this Problem
Given these two date systems have a difference of 1,462 days; it is fundamental to be mindful of the impact it could have on a financial model or any business spreadsheet. Some corporations that contain multiple business units or operations across locations or industries, it is possible there will be some financial modelling performed in both Microsoft Excel © and Excel for Mac ©. Hence this is how to correct the serial number problem.
Excel for Mac © into Microsoft Excel ©
These dates are understated by the delta of 1,462 days; therefore 1,462 must be added to the source serial number, which is imported from an Excel for Mac © (1904 date system) into the MS Excel (1900 date system) financial model.