Vacation Breakdown

TAPS_MikeDion

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
622
Office Version
  1. 2011
Platform
  1. MacOS
Hi everybody,

A new method of paying out vacation time was just implemented at my job, and since I'm the manager I need to execute it.

Here's what I'm hoping those of you that excel at Excel can help me with...

I have a spreadsheet with each employees vacation hours earned for the year (31.75, 108.00, 87.50, etc.)

I need to have a formula (or VBA code) that checks the date in the column (we'll use column C), which holds the employees date of hire, and breaks it down by years of service as shown below, and returns what is needed in column D (shown below.)

After 1 year of service = 2 weeks vacation
After 5 years of service = 3 weeks vacation
After 12 years of service = 4 weeks vacation
After 25 years of service = 5 weeks vacation

Column D needs to do the following:
Divide the earned hours by the # of weeks of vacation time, based on years of service.

For example:
Column B shows her earned vacation hours as 108.00 hours.
Column C for Jane Doe shows date of hire as 1/1/01.
This would mean Jane Doe gets 4 weeks of vacation, based on her 12 years of service.
Column D should then show 108.00/4, which would be 27.00 hours.

Thank you SO MUCH for any help you can give!

-Mike
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
<head><style type='text/css' >table{ width='100%' border: 2px solid #A52A2A; background-color: #B0EEAA;} th {background-color: #2F4F4F; color: white;} tr.one { background-color: #8FBC8F; color: #4F302F;} tr.two{ background-color: #F4A460; color: #4E4F2F;} </style></head><table><th>Earned Vacation Hours</th><th>Date of Hire</th><th>Date of Entitlement</th><th>Holiday Hour due</th><th></th><th>Years in job</th><th>Weeks Holidays entitled</th><tr class='one'><td>108</td><td>1/01/2001</td><td>29/01/2013</td><td>27</td><td></td><td>1</td><td>2</td></tr><tr class = 'two'><td></td><td></td><td></td><td></td><td></td><td>5</td><td>3</td></tr><tr class='one'><td></td><td></td><td></td><td></td><td></td><td>12</td><td>4</td></tr><tr class = 'two'><td></td><td></td><td></td><td></td><td></td><td>25</td><td>5</td></tr></table>

Years in Jobs and Weeks holiday entitled is not used in the formula but I put it there to show has the static arrays go accross rather than down. I used an extra column Date of entitlement as I could use today() to return the date but it will update automatically and used 365 days in the year.

Formula is:
=Earned_Vacation_Hours/VLOOKUP((Date_of_Entitlement-Date_of_Hire)/365,{1,2;5,3;12,4;25,5},2)

Let me know if this works for you. Did not test it that much, so you may want to check expected results works 100%
 
Upvote 0
Thanks for the work you did Sunny!

I'm waiting on a reply from HR to see if the entitlement day is going to be a static Jan 1st every year or if it is going to be based on the present day. I'll get back to this thread as soon as I know.

In the meantime, let's assume that it is going to be the first day of any given year (we'll go with 1/1/13 for this example.)

Date of hire: 1/1/01
Date of entitlement: 1/1/13
Years of service would be 12, so it needs to divide the 108 hours by 4 (weeks of vacation entitled), which would be 27.

The above information would all need to be shown on a single row, please. There would be no need to show all of the weeks available based on years of service, just the level that they're currently at.

Thanks SO MUCH for the help, it's greatly appreciated!

-Mike
 
Upvote 0
Hi Mike,

Was focussing too much about the little details:code and formula, not the bigger picture: results.

But trying to understand what you clarify in your last post, it occurred to me that the logic behind the division seems incorrect or that I am not getting the concept right:

if Jane Doe, 108 hours, 12 years employed get 108/4=27 then
Paul Young, 108 hours, 27 years employed get 108/5=21.6

Or may be Paul Young will always have a higher earned vacation hours???

So ... not sure this is the expected result....as poor Mr Young is getting less entitlement....and logically rewards should be higher, the longer in the job :)

PS: my formula is on one row and column to the right of 27 are not needed in the formula.
I was just putting the years in job and weeks holiday entitled to help me type the static array in my formula: {1,2;5,3;12,4;25,5}.
I guess I should have removed them before posting so I wouldn't confuse you. Sorry.

Waiting to hear more about your thoughts.
 
Last edited:
Upvote 0
Hey Sunny,

Yes, you are correct in your assumption regarding Jane Doe and Paul Young, with the exception that Paul Young would end up having more "earned hours." The spreadsheet is sent to me from HR and I have to implement this new method of calculating vacation time. I figured if I only had to add a couple of columns, copy and paste a formula into those columns to make this work, then I'm in heaven.

You have no idea how much I appreciate your help on this.

Thanks,
Mike
 
Upvote 0
Amended formula as if someone wasn't one year in a job the formula was returning an error [division by zero]. Use also the static value 01/01/2013 as the reference date for entitlements. Used also cells references rather then using named range.

Table Results
<table><tr><td></td><th>A</th><th>B</th><th>C</th><th>D</th></tr><tr><td>1</td><td bgcolor='#59BB9B'>Name</td><td bgcolor='#59BB9B'>Earned Vacation Hours</td><td bgcolor='#59BB9B'>Date of Hire</td><td bgcolor='#59BB9B'>Holiday Hour due</td></tr><tr><td>2</td><td bgcolor='#8B8787'>Jane Doe</td><td bgcolor='#8B8787'>108</td><td bgcolor='#8B8787'>1/01/2001</td><td bgcolor='#8B8787'>27.00</td></tr><tr><td>3</td><td bgcolor='#FFFFFF'>Paul Young</td><td bgcolor='#FFFFFF'>45</td><td bgcolor='#FFFFFF'>30/06/2007</td><td bgcolor='#FFFFFF'>15.00</td></tr><tr><td>4</td><td bgcolor='#8B8787'>Jo Blog</td><td bgcolor='#8B8787'>4.5</td><td bgcolor='#8B8787'>30/06/2012</td><td bgcolor='#8B8787'>0.00</td></tr></table>

Table with formulas
<table><tr><td></td><th>A</th><th>B</th><th>C</th><th>D</th></tr><tr><td>1</td><td bgcolor='#59BB9B'>Name</td><td bgcolor='#59BB9B'>Earned Vacation Hours</td><td bgcolor='#59BB9B'>Date of Hire</td><td bgcolor='#59BB9B'>Holiday Hour due</td></tr><tr><td>2</td><td bgcolor='#8B8787'>Jane Doe</td><td bgcolor='#8B8787'>108</td><td bgcolor='#8B8787'>1/01/2001</td><td bgcolor='#8B8787'>=IF(((DATEVALUE("01/01/2013")-C2)/365)<1,0,B2/VLOOKUP((DATEVALUE("01/01/2013")-C2)/365,{1,2;5,3;12,4;25,5},2))</td></tr><tr><td>3</td><td bgcolor='#FFFFFF'>Paul Young</td><td bgcolor='#FFFFFF'>45</td><td bgcolor='#FFFFFF'>30/06/2007</td><td bgcolor='#FFFFFF'>=IF(((DATEVALUE("01/01/2013")-C3)/365)<1,0,B3/VLOOKUP((DATEVALUE("01/01/2013")-C3)/365,{1,2;5,3;12,4;25,5},2))</td></tr><tr><td>4</td><td bgcolor='#8B8787'>Jo Blog</td><td bgcolor='#8B8787'>4.5</td><td bgcolor='#8B8787'>30/06/2012</td><td bgcolor='#8B8787'>=IF(((DATEVALUE("01/01/2013")-C4)/365)<1,0,B4/VLOOKUP((DATEVALUE("01/01/2013")-C4)/365,{1,2;5,3;12,4;25,5},2))</td></tr></table>
 
Upvote 0
Hey Sunny,

I just got back to my office a few hours ago. Thank you so much for this. I'll test it shortly and let you know how things turned out.

Right now, that looks PERFECT! :cool:

Thanks again,
Mike
 
Upvote 0
The angels are singing your name!!! :pray:

Thank you for everything Sunny, this is great!

-Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Tracking vacation requests
Replies
9
Views
919
KRice
K
S
  • Question Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
693
Sunshine8790
S
B
  • Solved
Looking to expand tool to track vacation pro-rations
Replies
1
Views
264
bh24524
B
J
  • Question Question
Payroll - Salary Step Increases
Replies
2
Views
305
JPav
J
C
  • Question Question
Calculating vacation entitlement and prorated amounts
2
Replies
17
Views
3K
Chrissy1911
C

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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