A ‘Quick and Dirty’ for Excel® dates before 1900

When working on the next post, which is about the ages of British Sovereigns and Prime Ministers (PMs), I came up against one of the limitations of the Excel® spreadsheet, namely that it can’t calculate dates prior to 1900. A sophisticated way of overcoming this restriction can be found here, but for my purposes I wanted something simpler. What follows is the ‘Quick and Dirty’ method I adopted, explained below as a simple spreadsheet.

In Line 3 the columns for the current PM show his birthdate and the date he took office. Cameron’s age at the time is then provided by the YEARFRAC function as shown.

Line 4 shows the same data for Harold Macmillan. Excel does not recognise his birthdate in 1894 and YEARFRAC returns a query.

This problem is overcome in Line 5 by adding 300 years to Macmillan’s dates, and YEARFRAC can then calculate his age when he first became PM.

As a check, in Line 6, after the same transformation to Cameron’s dates, YEARFRAC returns the same result as at Line 3.

NOTES

1. I only wanted results for age in years to one decimal place. The transformation will not be accurate if values in whole days are required because of Leap Year effects, as shown in Column E.
2. I used a 300 year increment because of the earliest date I was working with: 15 March 1779 (the birthdate of Queen Victoria’s first PM, Viscount Melbourne). I wanted to place all the transformed dates well in the future, so 200 years wouldn’t have been enough.
3. Fortunately all my dates were after the introduction of the Gregorian calendar (1752 in England).
4. Excel treats 1900 as a Leap Year – it wasn’t. However, none of my dates were for that year and at my required level of accuracy, again it wouldn’t matter.

Finally, if anyone does find this method useful (or useless for that matter), feel welcome to leave a comment.