Vacation Time Formula

[Stixbaraca]

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]

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]