Paid Time Off Automatically?

[nhbcai]

So. I'm trying to make a spreadsheet for my payroll person to help her keep track of our employees paid time off hours as they earn them.

How we do it here is as follows:
You work 80 hours, you get 4 hours of PTO. Hours roll over every week until you get 80, then they are reset to 0 and 4 hours are added to your PTO.

How would I go about making a spreadsheet that has our employees in A, their hours worked in B (or over B, C, D, E for each week of the month they work) and their PTO hours in C and have it add the PTO hours up when we type in hours worked and have it add up the PTO and reset the hours worked when it hits 80.

Any help on this would be appreciated.

Thanks for your time.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</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: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Employee</td><td style="font-weight: bold;text-align: center;;">Wk1</td><td style="font-weight: bold;text-align: center;;">Wk2</td><td style="font-weight: bold;text-align: center;;">Wk3</td><td style="font-weight: bold;text-align: center;;">Wk4</td><td style="font-weight: bold;text-align: center;;">Wk5</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Total</td><td style="font-weight: bold;text-align: center;;">PTO Hrs</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Joe</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">160</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Sue</td><td style="text-align: center;;">35</td><td style="text-align: center;;">25</td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">90</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Al</td><td style="text-align: center;;">20</td><td style="text-align: center;;">25</td><td style="text-align: center;;"></td><td style="text-align: center;;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">75</td><td style="text-align: center;;">0</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=SUM(<font color="Blue">B2:F2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=INT(<font color="Blue">H2/80</font>)*4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=SUM(<font color="Blue">B3:F3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=INT(<font color="Blue">H3/80</font>)*4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=SUM(<font color="Blue">B4:F4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=INT(<font color="Blue">H4/80</font>)*4</td></tr></tbody></table></td></tr></table><br />