Oct 04 2019 07:59 AM - edited Oct 04 2019 08:08 AM
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.
Oct 04 2019 08:10 AM
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.
Oct 04 2019 08:29 AM
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
Oct 04 2019 08:36 AM