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,020
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,240
Messages
5,339,031
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top