IF Formula Calculating Employee Benefits Packages

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This will work

MthhrsResultFormula
12401/8/06120=LOOKUP((INT((TODAY()-D2)/365))*12,$A$2:$B$6)
2480
60120
180160
240200

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Try:

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

where B2 is months of service.
 
Upvote 0
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>
 
Upvote 0
MthhrsStart DateResultFormula
124011/30/06120=LOOKUP((INT((TODAY()-D2)/365))*12,$A$2:$B$6)
2480120=LOOKUP((INT((TODAY()-D2)/365))*12,{12,40;24,80;60,120;180,160;240,200})
60120
180160
240200
=LOOKUP((INT((TODAY()-D2)/365))*12,{12,40;24,80;60,120;180,160;240,200})
ResultFormula
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>
 
Upvote 0
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.
 
Upvote 0
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 DateResultFormula
11/30/06120Hours 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 DateResultFormula
11/30/06 11/30/14Current Year Anniv.=DATE(YEAR(TODAY()),MONTH(A5),DAY(A5))
11/30/06 2922Days=DATE(YEAR(TODAY()),MONTH(A6),DAY(A6))-A6+1
11/30/06 08Years=TEXT(DATE(YEAR(TODAY()),MONTH(A7),DAY(A7))-A7+1,"Y")
11/30/06 84Months=TEXT(DATE(YEAR(TODAY()),MONTH(A8),DAY(A8))-A8,"Y")*12

<tbody>
</tbody>
 
Last edited:
Upvote 0
Correction for the Months:

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

<tbody>
</tbody>
 
Upvote 0
How about:

=LOOKUP(INT(YEARFRAC(A2,TODAY())*12),{0,0;12,40;24,80;60,120;180,160;240,200})
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top