Need to Calculate Employee Ages

[Motra]

Hi. I have a list of employees with their birthdates and need to do a formula to calculate their current ages rounded to two decimal places (25.48 -- not quite 25-1/2 years old as of today). And is there a way that I can make Excel recalculate the ages automatically (several months later, for example) without having to do another formula? Thank you!

 

[JulieD]

Hi Motra

i would use a cell to enter the date that you want the employees age
calculated at ... then all you have to do in 6 months time is change the
value in this cell

e.g.
A B C D E
Name DOB Age Date to Calc Age At: 4/6/2004
Bill 1/6/1970 =ROUND(($E$1-B2)/365.25,2)
Steve 5/3/1980 =ROUND(($E$1-B3)/365.25,2)

Hope this helps

Cheers
JulieD

Hi. I have a list of employees with their birthdates and need to do a

formula to calculate their current ages rounded to two decimal places
(25.48 -- not quite 25-1/2 years old as of today). And is there a way that
I can make Excel recalculate the ages automatically (several months later,
for example) without having to do another formula? Thank you!

 

[Norman Harker]

Hi Motra:

Basic formula is:

=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))


A1 is the birthdate and B1 the date that you want to calculate for.

We should be able to use YEARFRAC but it produces errors for cases of
over one year.

To round to two decimal places use:

=ROUND(DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),2)


If you want all ages to refer to a given future date, change the
reference to B1 to $B$1. Of course you can calculate the date in B1
however you like.

 

[Marcotte A]

----- JulieD wrote: ----

Hi Motr

i would use a cell to enter the date that you want the employees ag
calculated at ... then all you have to do in 6 months time is change th
value in this cel
<snip
Hope this help

Cheer
Julie