age calculation before 1900

[Roy40]

DATEDIF does not calculate age before 1900.

Is there a work around?

Roy

[#2]

@Roy40 

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

So for example

 

[#3]

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.

Roy

[#4]

@Roy40 

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.

[#5]

@Roy40 

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.

[#6]

@Roy40 

 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.

NikolinoDE

I know I don't know anything (Socrates)