Age calculation formula

[Eloise T]

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.)

 

[shrivallabha]

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())

 

[shrivallabha]

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!

 

[Eloise T]

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?



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

THANKS Shrivallabha!
View attachment 44601

 

[John Jairo V]

Hi, @Eloise T!

You could simply use:
=INT(YEARFRAC(C4,TODAY()))

or:
=DATEDIF(C4,TODAY(),"y")

Blessings!

 

[SirJB7]

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!

 

[bobhc]

.
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() ) )


.

 

[deciog]

Eloise T

My contribution

Decio

 

[Eloise T]

Hi, @Eloise T!

You could simply use:
=INT(YEARFRAC(C4,TODAY()))

or:
=DATEDIF(C4,TODAY(),"y")

Blessings!

Thank you, John!

 

[Eloise T]

Eloise T

My contribution

Decio

Eloise T
Thank you, Decio G!

 

[Eloise T]

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

 

[Eloise T]

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.

 

[SirJB7]

Hi, Eloise T!
I always post formulas in local and English languages.
Regards!

 

[Eloise T]

Hi, Eloise T!
I always post formulas in local and English languages.
Regards!

Unless I missed something, you only posted in Spanish!
A menos que me haya perdido algo, solo publicaste en español!

 

[SirJB7]

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!

 

[Eloise T]

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.

 

[deciog]

Eloise

Look at this link, I hope I have helped

Https://www.mrexcel.com/forum/excel-questions/1019606-duration-years-month-days-hours-minutes.html

Decio

 

[Eloise T]

Eloise

Look at this link, I hope I have helped

Https://www.mrexcel.com/forum/excel-questions/1019606-duration-years-month-days-hours-minutes.html

Decio

Good link. Thank you. That may come in handy in the future. I may have not made it clear but for this exercise in needed to round down to the nearest year.

 

[bobhc]

for this exercise in needed to round down to the nearest year

nope did not say that

but did say

s opened, the correct age appears.