Date

%3CLINGO-SUB%20id%3D%22lingo-sub-893567%22%20slang%3D%22en-US%22%3EDate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893567%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20calculate%20someone's%20age%20using%20their%20DOB%20and%20current%20time%20and%20date.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-893567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893607%22%20slang%3D%22en-US%22%3ERe%3A%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893607%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420207%22%20target%3D%22_blank%22%3E%40dre0954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20undocumented%20DATEDIF%20function%20to%20calculate%20age%20in%20years%2Fmonths%20etc.%20To%20display%20just%20age%20in%20years%2C%20you%20could%20use%3A%3C%2FP%3E%3CPRE%3E%3DDATEDIF(A1%2CTODAY()%2C%22y%22)%3C%2FPRE%3E%3CP%3ETo%20display%20the%20age%20in%20years%2C%20months%20and%20days%2C%20you%20could%20use%20something%20like%3A%3C%2FP%3E%3CPRE%3E%3DIF(DATEDIF(A1%2CTODAY()%2C%22y%22)%26lt%3B%26gt%3B0%2CDATEDIF(A1%2CTODAY()%2C%22y%22)%26amp%3B%22%20years%20%22%2C%22%22)%26amp%3BIF(DATEDIF(A1%2CTODAY()%2C%22ym%22)%26lt%3B%26gt%3B0%2CDATEDIF(A1%2CTODAY()%2C%22ym%22)%26amp%3B%22%20months%20%22%2C%22%22)%26amp%3BIF(DATEDIF(A1%2CTODAY()%2C%22md%22)%26lt%3B%26gt%3B0%2CDATEDIF(A1%2CTODAY()%2C%22md%22)%26amp%3B%22%20days%22%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3BHope%20that%20helped.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893652%22%20slang%3D%22en-US%22%3ERe%3A%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420207%22%20target%3D%22_blank%22%3E%40dre0954%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20DATEDIF()%20is%20documented%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdatedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdatedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DYEARFRAC(A1%2CTODAY()%2C3)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20INT()%20it%20if%20only%20full%20years%20are%20required%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893690%22%20slang%3D%22en-US%22%3ERe%3A%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893690%22%20slang%3D%22en-US%22%3EI%20know%20that.%20What%20I%20meant%20was%20that%20it%20is%20undocumented%20inside%20the%20application.%3C%2FLINGO-BODY%3E
dre0954
Occasional Visitor

I want to calculate someone's age using their DOB and current time and date. 

=(YEAR(NOW())-YEAR(C150)) this formula does not take into account the current time or day. 

23/11/2013 is returning age 6 now, it should return 5 now.

3 Replies

@dre0954 

You can use the undocumented DATEDIF function to calculate age in years/months etc. To display just age in years, you could use:

=DATEDIF(A1,TODAY(),"y")

To display the age in years, months and days, you could use something like:

=IF(DATEDIF(A1,TODAY(),"y")<>0,DATEDIF(A1,TODAY(),"y")&" years ","")&IF(DATEDIF(A1,TODAY(),"ym")<>0,DATEDIF(A1,TODAY(),"ym")&" months ","")&IF(DATEDIF(A1,TODAY(),"md")<>0,DATEDIF(A1,TODAY(),"md")&" days","")

 Hope that helped.

Highlighted

@dre0954 

By the way, DATEDIF() is documented - https://support.office.com/en-us/article/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

 

Another way is

=YEARFRAC(A1,TODAY(),3)

and INT() it if only full years are required

I know that. What I meant was that it is undocumented inside the application.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies