Calculating pensions rate

[Guest]

Salary DOB Age Date Date of employment 7.5% 10%


Hi there I have the above headings (Columns a-g) what I need it to do is
when employees are over 41 their pension increases to 10% rather than 7.5% so
what it needs to do is give a number in 7.5% for how long they've been in
employment and below 41 years old and how long they've been in employment and
41 and above.

Can anyone help?

 

[Bob Phillips]

=DATEDIF(E2,MAX(DATE(YEAR(B2)+41,MONTH(B2),DAY(B2)),E2),"Y")

and

=DATEDIF(MAX(DATE(YEAR(B2)+41,MONTH(B2),DAY(B2)),E2),TODAY(),"Y")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

This is great thanks - just a quicky - is there anyway to make it calculate
each one to 2 decimal places - or is this not possible? (Forgot to mention
that bit sorry!)

 

[Bob Phillips]

That is tricky, as DATEDIF only works on complete years.

If you want year fractions, you will need to calculate it all oneself, which
means that we need a rule (because some years have 365 days, some have 366).
So do we divide the number of days by 365, 365.25 or what?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

How about some examples with salaries>date desired>dob>and expected answer.