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

Raven26

New Member
Joined
Oct 10, 2006
Messages
37
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
Code:
=IF(B1>5110,200,IF(AND(B1>1461,B1<=5110),160,IF(B1<=1460,120,0)))
 

Raven26

New Member
Joined
Oct 10, 2006
Messages
37
Thank you! This was driving me crazy! The IF Statement worked great!
I'm not really familiar with the Vlook up.


Awesome!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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})
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,533
Members
410,547
Latest member
htran4
Top