bonus disqualifiers for excess vacation days

K

kiki burgh

New Member
Joined
Jun 9, 2014
Messages
44
hope all is well everyone. may i seek your help for coming up with a template for computing for disqualifiers for a certain bonus given the following conditions (that sadly i cannot convert to formula):

folks are given bonus for completing work days in a month; however, due to being understaffed, taking vacation leaves that go beyond 10% of work days in a month is discouraged.

base bonus amount = 2,903.336 (subject to change monthly but is fixed for this specific example)

# of worked days in a month = 21 (other possible values of days worked: 19, 20, 22, 23)

10% of days worked
19 = 1.9
20 = 2
21 = 2.1
22 = 2.2
23 = 2.3

# days in excess of 10% of work days will incur increasing deductions
1 day = less 25% of base amount
2 days = less 50% of base amount
3 days = less 75% of base amount
4 days = less 100% of base amount

for example, month of june has 21 work days. an employee with exactly 2 (to at most 2.1) days approved & plotted vacation, will be rewarded 100% of base bonus amount whereas an employee with who filed for 4 days of leaves (4/21 = 19% has .9 excess days is definitely more than 10% of 21 work days of the month), will still be rewarded bonus but 50% less of base bonus amount compared to the other employee in prior example.

kindly see sample table below. for results shown in the attachment, these were just manually computed & referenced to tables. any approach or presentation to make it even leaner/better, will be greatly appreciated.

Code: 
[TABLE="class: grid, width: 526"]
<tbody>[TR]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]A[/SIZE][/TD]
[TD][SIZE=1]B[/SIZE][/TD]
[TD][SIZE=1]C[/SIZE][/TD]
[TD][SIZE=1]D[/SIZE][/TD]
[TD][SIZE=1]E[/SIZE][/TD]
[TD][SIZE=1]F[/SIZE][/TD]
[TD][SIZE=1]G[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A1[/SIZE][/TD]
[TD][SIZE=1]base bonus amount[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,903.336[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A2[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A3[/SIZE][/TD]
[TD][SIZE=1]# of work days[/SIZE][/TD]
[TD="align: right"][SIZE=1]10%[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]excess days[/SIZE][/TD]
[TD][SIZE=1]deduction[/SIZE][/TD]
[TD][SIZE=1]amount[/SIZE][/TD]
[TD][SIZE=1]total[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A4[/SIZE][/TD]
[TD="align: right"][SIZE=1]19[/SIZE][/TD]
[TD="align: right"][SIZE=1]1.9[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD="align: right"][SIZE=1]1[/SIZE][/TD]
[TD="align: right"][SIZE=1]-25%[/SIZE][/TD]
[TD="align: right"][SIZE=1]-725.834[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,177.502[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A5[/SIZE][/TD]
[TD="align: right"][SIZE=1]20[/SIZE][/TD]
[TD="align: right"][SIZE=1]2[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD="align: right"][SIZE=1]2[/SIZE][/TD]
[TD="align: right"][SIZE=1]-50%[/SIZE][/TD]
[TD="align: right"][SIZE=1]-1451.668[/SIZE][/TD]
[TD="align: right"][SIZE=1]1,451.668[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A6[/SIZE][/TD]
[TD="align: right"][SIZE=1]21[/SIZE][/TD]
[TD="align: right"][SIZE=1]2.1[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD="align: right"][SIZE=1]3[/SIZE][/TD]
[TD="align: right"][SIZE=1]-75%[/SIZE][/TD]
[TD="align: right"][SIZE=1]-2177.502[/SIZE][/TD]
[TD="align: right"][SIZE=1]725.834
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A7[/SIZE][/TD]
[TD="align: right"][SIZE=1]22[/SIZE][/TD]
[TD="align: right"][SIZE=1]2.2[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD="align: right"][SIZE=1]4[/SIZE][/TD]
[TD="align: right"][SIZE=1]-100%[/SIZE][/TD]
[TD="align: right"][SIZE=1]-2903.336[/SIZE][/TD]
[TD="align: right"][SIZE=1]0.000[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A8[/SIZE][/TD]
[TD="align: right"][SIZE=1]23[/SIZE][/TD]
[TD="align: right"][SIZE=1]2.3[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A9[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A10[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A11[/SIZE][/TD]
[TD][SIZE=1]Agent[/SIZE][/TD]
[TD][SIZE=1]Leave(s)[/SIZE][/TD]
[TD][SIZE=1]bonus[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A12[/SIZE][/TD]
[TD][SIZE=1]Agent 01[/SIZE][/TD]
[TD="align: right"][SIZE=1]0[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,903.336[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A13[/SIZE][/TD]
[TD][SIZE=1]Agent 02[/SIZE][/TD]
[TD="align: right"][SIZE=1]0[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,903.336[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A14[/SIZE][/TD]
[TD][SIZE=1]Agent 03[/SIZE][/TD]
[TD="align: right"][SIZE=1]2[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,903.336[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A15[/SIZE][/TD]
[TD][SIZE=1]Agent 04[/SIZE][/TD]
[TD="align: right"][SIZE=1]6[/SIZE][/TD]
[TD="align: right"][SIZE=1]0[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A16[/SIZE][/TD]
[TD][SIZE=1]Agent 05[/SIZE][/TD]
[TD="align: right"][SIZE=1]1[/SIZE][/TD]
[TD="align: right"][SIZE=1]2,903.336[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A17[/SIZE][/TD]
[TD][SIZE=1]Agent 06[/SIZE][/TD]
[TD="align: right"][SIZE=1]4[/SIZE][/TD]
[TD="align: right"][SIZE=1]1451.668[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A18[/SIZE][/TD]
[TD][SIZE=1]Agent 07[/SIZE][/TD]
[TD="align: right"][SIZE=1]4[/SIZE][/TD]
[TD="align: right"][SIZE=1]1451.668[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A19[/SIZE][/TD]
[TD][SIZE=1]Agent 08[/SIZE][/TD]
[TD="align: right"][SIZE=1]3[/SIZE][/TD]
[TD="align: right"][SIZE=1]2177.502[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A20[/SIZE][/TD]
[TD][SIZE=1]Agent 09[/SIZE][/TD]
[TD="align: right"][SIZE=1]3[/SIZE][/TD]
[TD="align: right"][SIZE=1]2177.502[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1]A21[/SIZE][/TD]
[TD][SIZE=1]Agent 10[/SIZE][/TD]
[TD="align: right"][SIZE=1]5[/SIZE][/TD]
[TD="align: right"][SIZE=1]725.834[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[TD][SIZE=1]
[/SIZE][/TD]
[/TR]
</tbody>[/TABLE]

looking forward to your prompt help.
cheers.
 
Last edited:
Sort by date Sort by votes
Hey kiki
I get a 404 error at Dropbox........file not found !!!
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You must log in or register to reply here.

Similar threads

J
  • Solved
Formula Filled Dates
Replies
4
Views
347
Fluff
Fluff
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
121
Tetra201
Tetra201
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
361
mrshuck
M
B
  • Question
dynamic formula help - supply multiple variables and process row by row
Replies
0
Views
407
BrettJH
B
R
  • Question
Calculating Man Hours Worked on Day Night Shift
Replies
0
Views
2K
richy89
R

Forum statistics

Threads
1,215,548
Messages
6,125,468
Members
449,230
Latest member
ASBeard

Share this page

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