age calculation before 1900

New Contributor

DATEDIF does not calculate age before 1900.

Is there a work around?



5 Replies


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

So for example


First & Middle Name Maiden Married Born Death Age Age
John McClelland Lindsey-1840 Lindsey 4/25/1840 1/8/1908 68.0 #VALUE! using DATEDIF
William Lindsey-1811 Lindsey 6/14/1811 1/24/1882 71.0 70 using death-born year
Carl Harold Lindsey-1909 Lindsey 5/24/1909 4/19/1981 71.8 71 DATEIF
Augusta "Lamb" Lindsey-1844 Lamb Lindsey 7/1/1844 7/1/1916 72.0 72
William Bowden Bray Jr.-1938 Bray 6/12/1938 8/13/2012 74.2 74
Carol Jean "Lindsey" Eads-1931 Lindsey Eads 4/3/1931 4/16/2007 76.0 81

Adding years would work but very confusing.



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:


where D2 is date of birth, E2 is date of death.


As @Hans Vogelaar 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.


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.


 In addition to the example of Mr. @Hans Vogelaar  here with VBA

How to calculate ages before 1/1/1900 in Excel

...although the simplest and faster solution proposed by Mr. @Hans Vogelaar.



I know I don't know anything (Socrates)