Birthdate Calculations w/Set Stop Date

[dasbear3]

Hello Everyone,

I am looking for a formula that calculates an individuals exact age given their date of birth, that shows their age in years (possibly with two decimal points.)

I have seen the formulas using the TODAY() option, however I will need the stop date to be effective 05-JAN-08.

An example of that would be an employee with a birthdate of 12-JAN-58 would still be shown as 49.99. Would I need to add a column with just the ending date, or is there a way to throw the 05-JAN-08 date in the formula?

Thank you! Any advice would be greatly appreciated!

 

[iliace]

How did you get 49.99?

 

[lefty78312]

How about putting 05 Jan 08 in one cell; say A1, and then refer to it in the formula. For example, if the first individual's DOB is in B1, your formula would be =a1-b1, etc. I forgot how to format the answer (help, someone!), but if you format it correctly, it will give you the answer you want.

 

[KRM3]

Couple of ways:

First, I think you need to go to tools -> Add ins... and check/turn on Analysis ToolPak, it will make some additional date functions available among other things that will be helpful.

1) hard coded in cell using the date function:
formula = (date(2008,1,5)-date(1958,1,12))/365.25

2) with cell references:
A1 = date of birth [1/12/58]
A2 = end date [1/5/08]
formula = (A2-A1)/365.25

It's not perfect due to leap years etc, which is why you divide by 365.25 days - 1 extra day every 4 years.

3) DATEDIF function - should be built in to excel but it's not documented. It will return the age as an integer.
formula = datedif(A1,A2,"y")