# datedif for years > 1900

Occasional Visitor

# datedif for years > 1900

I try to user datediff for people who lived before 1/1/1900; this does not work as excel considers 1/1/1900 as day one. How can I get the result for someone who lived say from 1/3/1600 to 5/5/1665 ??

2 Replies

# Betreff: datedif for years > 1900

=DATEDIF(IF(ISERROR(YEAR(A2));LEFT(A2;6)&RIGHT(A2;4)+400;A2+(400*365.24));IF(ISERROR(YEAR(B2));LEFT(B2;6)&RIGHT(B2;4)+400;B2+(400*365.24));"Y")&" Years"

Example in the file

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

# Re: datedif for years > 1900

Here is How to calculate ages before 1/1/1900 in Excel macro from Microsoft with step by step instruction how to use it.

Other variant is Power Query which natively works with such dates.

And different kind of formulas like @Nikolino  suggested.

You may check pre-1900 dates thread here where above variants are discussing.