We help IT Professionals succeed at work.

Turn date into age

134 Views
Last Modified: 2017-03-12
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.
Comment
Watch Question

Author

Commented:
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.
akb
CERTIFIED EXPERT

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.

Author

Commented:
Thanks for your help.  I don't mean to be discourteous.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Please find attached for your reference:
Dier-Eloum-Age-Calculation_v1.xlsx

Author

Commented:
=DATEDIF(B2, TODAY(), "y")+1 on date 14th-jun- 2005 Gave me 12th Jan 1900.

Author

Commented:
Sorry in spreadsheet it is 14-jun-2005
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Format cell to general
akb
CERTIFIED EXPERT

Commented:
Maybe I'm missing something but I'm pretty sure that my formula meets your requirement.

Author

Commented:
Did that but still not working.  Tried number as well.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Please see below: I am following the formula and formatting cell to general:
Age
akb
CERTIFIED EXPERT

Commented:
Please give us a few examples of what you want.

Author

Commented:
Thanks.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Dier! Glad it helped.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi Dier,

This would be better way instead of adding one:
=ROUNDUP(YEARFRAC(B2, TODAY(), 1), 0)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.