Need to Calculate Employee Ages

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Motra said:
Hi. I have a list of employees with their birthdates and need to do a
Click to expand...
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!
 
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.
 
----- 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
 
Back
Top