New Contributor

# age calculation before 1900

DATEDIF does not calculate age before 1900.

Is there a work around?

Roy

5 Replies

# Re: age calculation before 1900

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

So for example

# Re: age calculation before 1900

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

Adding years would work but very confusing.

Roy

# Re: age calculation before 1900

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.

# Re: age calculation before 1900

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.

# Re: age calculation before 1900

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)