Link to home
Start Free TrialLog in
Avatar of Dier Eluom
Dier Eluom

asked on

Turn date into age

I want to get a date like 23-Nov-2008 and get it to show the persons age this year - so not the age they are now if their birthday was in June but what they turn this year.
Avatar of Dier Eluom
Dier Eluom

ASKER

OK, must be a bit tired.  So if they were born in November this year I want to know their age this year, not their age today - i.e turning 10 not 9 now.
Assuming the date of birth is in A1, put the following in B1:
=+YEAR(NOW())-YEAR(B1)
Make sure B1 is formatted as General or Number
Avatar of Shums Faruk
Hi Dier,

There are few ways of calculating age from the date, assuming your DOB is in B2:
1st Way:
=INT((TODAY()-B2)/365)

Open in new window

2nd Way:
=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)

Open in new window

3rd Way:
=DATEDIF(B2, TODAY(), "y")

Open in new window

4th way, If you want to to calculate age from date of birth in years, months and days:
=IF(DATEDIF(B2, TODAY(),"y")=0,"",DATEDIF(B2, TODAY(),"y")&" Years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" Months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" Days")

Open in new window

5th Way, if you want to calculate age in a certain years, months and days:
=IF(DATEDIF(B2,DATE(C2, 1, 1),"y")=0,"",DATEDIF(B2,DATE(C2, 1, 1),"y")&" Years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" Months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" Days")

Open in new window

Hope this helps!

Please find attached for your reference
Dier-Eloum-Age-Calculation.xlsx
My question asks what age THIS YEAR, not as of formula insertion.  Someone born on the 14th June 2005 is now 11 but turns 12 this year.  None of your formulas meet that criteria.
Thanks for your help.  I don't mean to be discourteous.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please find attached for your reference:
Dier-Eloum-Age-Calculation_v1.xlsx
=DATEDIF(B2, TODAY(), "y")+1 on date 14th-jun- 2005 Gave me 12th Jan 1900.
Sorry in spreadsheet it is 14-jun-2005
Format cell to general
Maybe I'm missing something but I'm pretty sure that my formula meets your requirement.
Did that but still not working.  Tried number as well.
Please see below: I am following the formula and formatting cell to general:
User generated image
Please give us a few examples of what you want.
Thanks.
You're Welcome Dier! Glad it helped.
Hi Dier,

This would be better way instead of adding one:
=ROUNDUP(YEARFRAC(B2, TODAY(), 1), 0)