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.

@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

Highlighted
I know that. What I meant was that it is undocumented inside the application.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies