Multiple criteria formula

[tsh1586]

I need to write a formula that will look at someone's hire date (C3) and if that is before 09/01/07 then it will calculate number of years they will have been here I have the year end date in C1. Based on number of years it takes salary (J3) and multiplies by %
the years are broke down with the following
5-9 years .5%
10-14 1%
15-19 1.5%
20 and more 2%

Any help would be greatly appreciated.

 

[Special-K99]

Re: Help with multiple criteria formula

You can obtain the correct percentage by using this LOOKUP formula.

=LOOKUP(A1,{0,5,10,15,20},{0,0.05,0.1,0.15,0.2})

where A1 is the number of years.

Not sure about the rest of the problem though.

 

[tsh1586]

I need to write a formula that will look at someone's hire date (C3) and if that is before 09/01/07 then it will calculate number of years they will have been here I have the year end date in C1. Based on number of years it takes salary (J3) and multiplies by %
the years are broke down with the following
5-9 years .5%
10-14 1%
15-19 1.5%
20 and more 2%

Any help would be greatly appreciated.

I was able to use the following formula to calculate the rate but I can't seem to get anything to work when trying to add the and statement of the year being older than a specific date.

=IF(DATEDIF($C$3,$C$1,"y")>19,0.02,IF(DATEDIF($C$3,$C$1,"y")>14,0.015,IF(DATEDIF($C$3,$C$1,"y")>9,0.01,IF(DATEDIF($C$3,$C$1,"y")>4,0.005)))*$J$3)

Please help this is driving me bonkers!!

 

[GR00007]

I hope this may help.
=LOOKUP(IF(C2>39326,0,($A$1 -C2) / 365.25),{0,5,10,15,20},{0,0.005,0.01,0.015,0.02})

Taking Special-K's lookup array to get the rate based on years worked tried to meet your request.
- A1 has year end date (12/31/2019)
- 39326 is the date value of 9/1/2007
- this will return the percentage based on years worked, with any hiredate after 9/1/2007 being zero.