datedif for years > 1900

%3CLINGO-SUB%20id%3D%22lingo-sub-1938570%22%20slang%3D%22en-US%22%3Edatedif%20for%20years%20%26gt%3B%201900%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938570%22%20slang%3D%22en-US%22%3E%3CP%3EI%20try%20to%20user%20datediff%20for%20people%20who%20lived%20before%201%2F1%2F1900%3B%20this%20does%20not%20work%20as%20excel%20considers%201%2F1%2F1900%20as%20day%20one.%20How%20can%20I%20get%20the%20result%20for%20someone%20who%20lived%20say%20from%201%2F3%2F1600%20to%205%2F5%2F1665%20%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1938570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938606%22%20slang%3D%22de-DE%22%3ESubject%3A%20datedif%20for%20years%20%26gt%3B%201900%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938606%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884733%22%20target%3D%22_blank%22%3E%40hhm-7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATEDIF(IF(ISERROR(YEAR(A2))%3B%20LEFT(A2%3B6)%26amp%3BRIGHT(A2%3B4)%2B400%3B%20A2%2B(400*365.24))%3BIF(ISERROR(YEAR(B2))%3B%20LEFT(B2%3B6)%26amp%3BRIGHT(B2%3B4)%2B400%3B%20B2%2B(400*365.24))%3B%22%20Y%22)%26amp%3B%22%20Years%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%22%3E%3CSPAN%3EExample%20in%20the%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@hhm-7 

 

=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.

@hhm-7 

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.