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