age calculation before 1900

Question

DATEDIF does not calculate age before 1900.Is there a work around?&nbsp;Roy

hansvogelaar · Answer

Roy40&nbsp;
Excel does not work with dates before 1900. You could add (for example) 400 years to both dates.
So for example
&nbsp;

roy40 · Answer

First &amp; Middle Name Maiden Married Born Death Age AgeJohn McClelland Lindsey-1840 Lindsey 4/25/1840 1/8/1908 68.0 #VALUE! using DATEDIFWilliam Lindsey-1811 Lindsey 6/14/1811 1/24/1882 71.0 70 using death-born yearCarl Harold Lindsey-1909 Lindsey 5/24/1909 4/19/1981 71.8 71 DATEIFAugusta "Lamb" Lindsey-1844 Lamb Lindsey 7/1/1844 7/1/1916 72.0 72William Bowden Bray Jr.-1938 Bray 6/12/1938 8/13/2012 74.2 74Carol Jean "Lindsey" Eads-1931 Lindsey Eads 4/3/1931 4/16/2007 76.0 81Adding years would work but very confusing.Roy

hansvogelaar · Answer

Roy40&nbsp;
You could use a custom VBA function:
Function Age(d1, d2) As Double
    Age = (CDate(d2) - CDate(d1)) / 365.2422
End Function
Use like this:
=AGE(D2,E2)
where D2 is date of birth, E2 is date of death.

sergeibaklan · Answer

Roy40&nbsp;
As&nbsp;HansVogelaar&nbsp;mentioned Excel calendar starts from 1900 if your are on Windows and from 1904 on Mac. For previous dates you need a workaround, otherwise they are not recognized. We have what we have.
&nbsp;
Alternatively you may use VBA or Power Query, they work from year 100. If you have any persons before that we still need some workaround.

nikolinode · Answer

Roy40&nbsp;&nbsp;In addition to the example of Mr. @Hans Vogelaar &nbsp;here with VBAHow to calculate ages before 1/1/1900 in Excel...although the simplest and faster solution proposed by Mr. @Hans Vogelaar.&nbsp;NikolinoDEI know I don't know anything (Socrates)

Forum Discussion

age calculation before 1900

5 Replies

Resources