I need to create an IF formula w/ multiple date Criteras

[Raven26]

What I am trying to do is figure out an IF statement for the following. I have a number of employees who receive vacation at 120/hrs, 160/hrs & 200/hrs.

The criteria is:
1-4yrs is 120/hrs
5-14yrs is 160/hrs
15+ is 200/hrs

Now I have a column with the number of days (B1) the employee has been employeed. In another column (A1) Is where I want the rate to show up using the if statement.

i.e. IF B1 is between 0-1460/days then its 120,
IF between 1461-5110/days then its160, &
IF greater then 5110/day its 200

 

[Gates Is Antichrist]

Do you know how to use VLOOKUP? If so, use ,TRUE as the fourth argument, and this fits beautifully.

 

[texasalynn]

try this

=IF(B1>5110,200,IF(AND(B1>1461,B1<=5110),160,IF(B1<=1460,120,0)))

 

[Raven26]

Thank you! This was driving me crazy! The IF Statement worked great!
I'm not really familiar with the Vlook up.


Awesome!

 

[toy4x4]

Greater Than and Less than

Ooops

 

[barry houdini]

With LOOKUP

=LOOKUP(B1,{0,1461,5111;120,160,200})

although 5110 days doesn't really equal 14 years because you would have some leap years in there. You could work out the leave entitlement directly from the employee's hire date with this formula, assuming hire date in C1

=LOOKUP(DATEDIF(C1,TODAY(),"y"),{0,4,14;120,160,200})