Vacation Time Formula

Stixbaraca

New Member
Joined
Oct 14, 2019
Messages
1
First off, I apologize if this question has already been asked and answered! I am looking for a formula to determine employee vacation time. Our VT is distributed in hours not days, and no hours can be carried over to the next calendar year. My spreadsheet already has employment start dates at B2 through B20 (small company!) My vacation time values will be listed in F2 through F20. Our annual vacation is determined by years of service. All employee's vacation hours for the current year are earned as of the employee's anniversary date. Below is the amount of hours per year.

1 year through 5 years of service - 80 Hours vacation
6 years through 11 years of service - 120 Hours vacation
12 years through 16 years of service - 180 Hours vacation
17 years through 20 years of service - 200 Hours Vacation
21 years of service or more - 240 Hours vacation

Thanks a million for all the help!
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,023
Office Version
365
Platform
Windows
Welcome to the forum.

I took the time to create some sample data. Copy the formulas downwards.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;background-color: #FCE4D6;;">10/15/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #FFF2CC;;">Workman Start Date</td><td style="background-color: #FFF2CC;;">Days of Service</td><td style="background-color: #FFF2CC;;">Years of Service</td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Vacation Hours</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1/1/1997</td><td style="text-align: right;background-color: #E2EFDA;;">8322</td><td style="text-align: right;background-color: #E2EFDA;;">22.8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">240</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">4/9/2001</td><td style="text-align: right;background-color: #E2EFDA;;">6763</td><td style="text-align: right;background-color: #E2EFDA;;">18.5</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">12/13/2007</td><td style="text-align: right;background-color: #E2EFDA;;">4324</td><td style="text-align: right;background-color: #E2EFDA;;">11.8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1/28/2008</td><td style="text-align: right;background-color: #E2EFDA;;">4278</td><td style="text-align: right;background-color: #E2EFDA;;">11.7</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">8/19/2009</td><td style="text-align: right;background-color: #E2EFDA;;">3709</td><td style="text-align: right;background-color: #E2EFDA;;">10.2</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">12/29/2018</td><td style="text-align: right;background-color: #E2EFDA;;">290</td><td style="text-align: right;background-color: #E2EFDA;;">0.8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;;"></td><td style="background-color: #DDEBF7;;">Years of Service</td><td style="background-color: #DDEBF7;;">Vacation Hours</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">4/30/2003</td><td style="text-align: right;background-color: #E2EFDA;;">6012</td><td style="text-align: right;background-color: #E2EFDA;;">16.5</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">3/14/2016</td><td style="text-align: right;background-color: #E2EFDA;;">1310</td><td style="text-align: right;background-color: #E2EFDA;;">3.6</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">80</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">4/10/2015</td><td style="text-align: right;background-color: #E2EFDA;;">1649</td><td style="text-align: right;background-color: #E2EFDA;;">4.5</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">120</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">8/28/1997</td><td style="text-align: right;background-color: #E2EFDA;;">8083</td><td style="text-align: right;background-color: #E2EFDA;;">22.1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">240</td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td><td style="text-align: right;;">180</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">4/3/2017</td><td style="text-align: right;background-color: #E2EFDA;;">925</td><td style="text-align: right;background-color: #E2EFDA;;">2.5</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">80</td><td style="text-align: right;;"></td><td style="text-align: right;;">17</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">6/28/2009</td><td style="text-align: right;background-color: #E2EFDA;;">3761</td><td style="text-align: right;background-color: #E2EFDA;;">10.3</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;">21</td><td style="text-align: right;;">240</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">3/15/2019</td><td style="text-align: right;background-color: #E2EFDA;;">214</td><td style="text-align: right;background-color: #E2EFDA;;">0.6</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">6/25/2000</td><td style="text-align: right;background-color: #E2EFDA;;">7051</td><td style="text-align: right;background-color: #E2EFDA;;">19.3</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">8/19/1998</td><td style="text-align: right;background-color: #E2EFDA;;">7727</td><td style="text-align: right;background-color: #E2EFDA;;">21.2</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">240</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">8/1/2003</td><td style="text-align: right;background-color: #E2EFDA;;">5919</td><td style="text-align: right;background-color: #E2EFDA;;">16.2</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">11/14/2010</td><td style="text-align: right;background-color: #E2EFDA;;">3257</td><td style="text-align: right;background-color: #E2EFDA;;">8.9</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">5/13/2004</td><td style="text-align: right;background-color: #E2EFDA;;">5633</td><td style="text-align: right;background-color: #E2EFDA;;">15.4</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">8/31/2006</td><td style="text-align: right;background-color: #E2EFDA;;">4793</td><td style="text-align: right;background-color: #E2EFDA;;">13.1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">180</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">12/17/2007</td><td style="text-align: right;background-color: #E2EFDA;;">4320</td><td style="text-align: right;background-color: #E2EFDA;;">11.8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet44</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=$B$1-B4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=C4/365</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D4,$H$10:$I$15,2</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 

Forum statistics

Threads
1,078,442
Messages
5,340,305
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top