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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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})
 

Forum statistics

Threads
1,141,151
Messages
5,704,586
Members
421,358
Latest member
Redstar13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top