# IF Formula Calculating Employee Benefits Packages

#### Justinian

##### Well-known Member
I need an formula to calculate employee vacation packages. The requirements are:

- 12 months of service = 40 hours vacation
- 24 months of service = 80 hours vacation
- 60 months of service = 120 hours vacation
- 180 months of service = 160 hours vacation
- 240 months of service = 200 hours vacation

I have the employees 2014 anniversary date but I cannot figure out how to sturcture my IF formula. So basically, if an employee was hired 1/8/06, I need the formula to spit out the appropriate vacation hour balance based on the chart above, which would be 120 hours because eight years of service falls between 60 months and 180 months.

Any ideas?

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### vogel997

##### Active Member
This will work

 Mth hrs Result Formula 12 40 1/8/06 120 =LOOKUP((INT((TODAY()-D2)/365))*12,\$A\$2:\$B\$6) 24 80 60 120 180 160 240 200

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

#### Teeroy

##### Well-known Member
Try:

=LOOKUP(B2,{0,0;12,40;24,80;60,120;180,160;240,200})

where B2 is months of service.

#### Justinian

##### Well-known Member
Hmmm, that did not work for me. I My chart looks like this:

Start Date
11/30/06

Anniversary Date
11/30

 Union Vacation Targets 12 = 40 hours 180 = 160 hours 24 = 80 hours 240 = 200 hours 60 = 120 hours

<tbody>
</tbody><colgroup><col span="8"></colgroup>

#### vogel997

##### Active Member
 Mth hrs Start Date Result Formula 12 40 11/30/06 120 =LOOKUP((INT((TODAY()-D2)/365))*12,\$A\$2:\$B\$6) 24 80 120 =LOOKUP((INT((TODAY()-D2)/365))*12,{12,40;24,80;60,120;180,160;240,200}) 60 120 180 160 240 200 =LOOKUP((INT((TODAY()-D2)/365))*12,{12,40;24,80;60,120;180,160;240,200}) Result Formula 7 full years =INT((TODAY()-D2)/365) 84 month =INT((TODAY()-D2)/365)*12 120 hours ,=LOOKUP((INT((TODAY()-D2)/365))*12,{12,40;24,80;60,120;180,160;240,200})

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

#### Justinian

##### Well-known Member
Is there a way to use just one formula? I was thinking something like this:

Starting Date
11/30/06 (cell A2)

Anniversary Date
11/30/14 (cell B2)

IF(B2>A2+365,"40,"") but I cannot figure out the rest of the formula.

#### vogel997

##### Active Member
To have an "if" formula, in this case you need to nest 5 if formulas into one and it can be very long. I suggest to use the following formula and the only reference is to "A2" and based on the current anniversary date.

 Hiring Date Result Formula 11/30/06 120 Hours Vacation =LOOKUP(TEXT(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-A2+1,"Y")*12,{12,40;24,80;60,120;180,160;240,200})

<tbody>
</tbody>

Here is how to calculate the months that is in the formula above:
 Hiring Date Result Formula 11/30/06 11/30/14 Current Year Anniv. =DATE(YEAR(TODAY()),MONTH(A5),DAY(A5)) 11/30/06 2922 Days =DATE(YEAR(TODAY()),MONTH(A6),DAY(A6))-A6+1 11/30/06 08 Years =TEXT(DATE(YEAR(TODAY()),MONTH(A7),DAY(A7))-A7+1,"Y") 11/30/06 84 Months =TEXT(DATE(YEAR(TODAY()),MONTH(A8),DAY(A8))-A8,"Y")*12

<tbody>
</tbody>

Last edited:

#### vogel997

##### Active Member
Correction for the Months:

 11/30/06 96 Months =TEXT(DATE(YEAR(TODAY()),MONTH(A8),DAY(A8))-A8+1,"Y")*12

<tbody>
</tbody>

#### Teeroy

##### Well-known Member

=LOOKUP(INT(YEARFRAC(A2,TODAY())*12),{0,0;12,40;24,80;60,120;180,160;240,200})

#### Justinian

##### Well-known Member
You are correct, Vogel, but I just used this formula:

=IF(AND(X24>=X20+365*1,X24<X20+365*2),40,IF(AND(X24>=X20+365*2,X24<X20+365*5),80,IF(AND(X24>=X20+365*1,X24<X20+365*15),120,IF(AND(X24>=X20+365*1,X24<X20+365*20),160,IF(X24>=X20+365*20,200,"")))))

I do not have room to chart my data like you stated above or maybe I am just not understanding how you were explaining it.

Replies
7
Views
2K
Replies
6
Views
2K
Replies
1
Views
587
Replies
1
Views
902
Replies
0
Views
942

1,171,509
Messages
5,875,933
Members
433,161
Latest member
CA_Raunak

### 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.

### Which adblocker are you using?

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

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