IF Formula Calculating Employee Benefits Packages

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
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?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
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>
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 22, 2010
Messages
412

ADVERTISEMENT

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>
 

Justinian

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

ADVERTISEMENT

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:

vogel997

Active Member
Joined
Jan 22, 2010
Messages
412
Correction for the Months:

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

<tbody>
</tbody>
 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
How about:

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

Justinian

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

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,551
Members
414,077
Latest member
ammylar5

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