• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Age calculation formula

Eloise T

Active Member
I have a spreadsheet (see uploaded file) which is a scrubbed and vastly shortened employee list, column A, with there DOB (date of birth) in the adjacent column (B). I would like to have Excel calculate their age in the Age column (C) based on today's date, such that whenever the spreadsheet is opened, the correct age appears.

Thank you.

P.S. I'm using Excel 2010 for this. I think there's a cool way to do it in 2013 and >

=DAYS(TODAY(),C4) / 365.25

(or exactly 365.2422 for you purists.)
 

Attachments

  • Chandoo - employee age chart.xlsx
    13 KB · Views: 5
Last edited:
Depends on how simple or complicated you need it to be!

See if results calculated by below formula in D4 suit or not:
=YEARFRAC(C4,TODAY())
 
Change the format to Number or General (Excel applies Date formatting by default if a formula involves DATE related function) and then check the results!
 
Change the format to Number or General (Excel applies Date formatting by default if a formula involves DATE related function) and then check the results!
I figured it out (format error) close to the time you replied. How do I round down to the nearest year? I tried to use Number format but it rounded up at .5 or .6?

upload_2017-8-15_13-5-22.png

Asked and answered... (I love www.excelfunctions.net)

THANKS Shrivallabha!
View attachment 44601
upload_2017-8-15_13-25-37.png
 
Last edited:
Hi, Eloise T!
Another option:
=AÑO(HOY())-AÑO(C4)-(MES(HOY())-MES(C4)<0)*1-Y(MES(HOY())-MES(C4)=0;DIA(HOY())-DIA(C4)<0)*1 -----> in English: =YEAR(TODAY())-YEAR(C4)-(MONTH(TODAY())-MONTH(C4)<0)*1-AND(MONTH(TODAY())-MONTH(C4)=0,DAY(TODAY())-DAY(C4)<0)*1
Regards!
 
.
The number of years between two dates, start_date and end_date is calculated as follows:
=YEARFRAC( start_date, end_date ), or cell reference


If you want to tidy up the above age calculation, so that it returns an integer, rather than a decimal value:
=INT( YEARFRAC( B1, B2 ) )


Alternatively, you could use the Today function directly in the age formula, so that the Excel age calculation formula becomes:
=INT( YEARFRAC( B1, TODAY() ) )


.
 
Eloise T

My contribution

Decio
 

Attachments

  • Chandoo - employee age chart Decio.xlsx
    14.3 KB · Views: 6
.
The number of years between two dates, start_date and end_date is calculated as follows:
=YEARFRAC( start_date, end_date ), or cell reference


If you want to tidy up the above age calculation, so that it returns an integer, rather than a decimal value:
=INT( YEARFRAC( B1, B2 ) )


Alternatively, you could use the Today function directly in the age formula, so that the Excel age calculation formula becomes:
=INT( YEARFRAC( B1, TODAY() ) )


.
Thanks Bob! I appreciate your options. Just goes to show how many possibilities there are to "skin a cat" in Excel.
 
Hi, Eloise T!
Another option:
=AÑO(HOY())-AÑO(C4)-(MES(HOY())-MES(C4)<0)*1-Y(MES(HOY())-MES(C4)=0;DIA(HOY())-DIA(C4)<0)*1 -----> in English: =YEAR(TODAY())-YEAR(C4)-(MONTH(TODAY())-MONTH(C4)<0)*1-AND(MONTH(TODAY())-MONTH(C4)=0,DAY(TODAY())-DAY(C4)<0)*1
Regards!
Very funny, Sir JB7. My Excel only interprets English at the moment.
 
Hi, Eloise T!
I think your glasses are needing an urgent upgrade.
You read this:
=AÑO(HOY())-AÑO(C4)-(MES(HOY())-MES(C4)<0)*1-Y(MES(HOY())-MES(C4)=0;DIA(HOY())-DIA(C4)<0)*1
and missed this, which was just following:
-----> in English: =YEAR(TODAY())-YEAR(C4)-(MONTH(TODAY())-MONTH(C4)<0)*1-AND(MONTH(TODAY())-MONTH(C4)=0,DAY(TODAY())-DAY(C4)<0)*1
BTW, nice Spanish :)
Regards!
 
Hi, Eloise T!
I think your glasses are needing an urgent upgrade.
You read this:

and missed this, which was just following:

BTW, nice Spanish :)
Regards!
So you did...I saw the Spanish at the beginning and my eyes passed right over the English.
 
Back
Top