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

Calculating Age with a Twist

A

AZExcel

Member
A co-worker and I have been working on this problem for a while and only achieved marginal success. Here' the issue. The sheet requires the user to provide the Date of Birth or (DOB), to arrive at what their age will be at MMI( a date in the future.


Here's the twist. There is a term called rated age that basically says if I am a 49 year old person but have conditions related to my lifestyle, chronic disease etc my actual or rated age is older than 49... For this example lets use 52.


So the question is, If my rated age is 52, and I will be MMI in 3yrs, how can I get the formula to return how old my rated age will be taking into consideration the date of birth?. below is an example of the form and what we have tried so far..


Thanks for any help that can be provided


DOB 12/27/1961(E8)

Current Rated Age 52(G8)

Todays Date 09/06/10


Rated Age at MMI ?

-------------------------------------------------------------------------------


MMI date 12/27/13(E14)


=INT(((E14-TODAY())/365.25)+((TODAY()-E8)/365.25))+((G8)-(((TODAY()-E8)/365.25)))
 
Lets see if this logic is right?

You want the MMI Age at a date in the future (E14) if the MMI age today is greater than his real age?


If that is correct try this:

=MAX(G8,INT((TODAY()-E8)/365.25))+INT((E14-TODAY())/365.25)
 
Hui:


Thanks for the reply. Let me clarify my question.


The age I will be working with will always be greater than the real age.


In the example above the age 52(rated age) is approximately 4 yrs above the real age.. how can I get the formula to return how old my rated age will be in the future taking into consideration the date of birth?


so lets say the date in the future is 3 yrs on 12/27/2013, the rate age would be 55 but if the future date was 12/26/2013 it would be 54..(if I can get the formula works correctly..


Thanks again for your response
 
If your rated age today is in G8 and is 52 Yrs

then in the future

=G8+INT((E14-TODAY())/365.25)

Where E14 is the future date


I would use

=G8+(E14-TODAY())/365.25

as using Int will remove a lot of data which you may want
 
You must log in or register to reply here.
Back
Top