Forum Discussion
age calculation before 1900
DATEDIF does not calculate age before 1900. Is there a work around? Roy
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.
RoyAs HansVogelaar 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.
You could use a custom VBA function:
Function Age(d1, d2) As Double Age = (CDate(d2) - CDate(d1)) / 365.2422 End FunctionUse like this:
=AGE(D2,E2)
where D2 is date of birth, E2 is date of death.
