Hey guys! Im new here and am getting really frustrated trying to make my formula work. I need to accrue vacation time for my full time employees. At hire date anniversary, they get 40hrs and accrue 3.33hrs per pay period. I need to figure out the formula for my balance column at the end, AF6. TIA!
<tbody>
</tbody>
A6 | B6 | C6 | D6 | E6 | F6 | AF6 | |||||||||||
Employee # | Employee Name | Date of Hire | Vacation as of 6/10/2017 | Accrual Rate | Pay Period 6/25 | 7/10 | 3/10 | 3/25 | 4/10 | 4/25 | 5/10 | 5/25 | 6/10 | 6/25 | 7/10 | Vacation Days Balance | |
1036 | AVALOS, ROSS | 6/11/2012 | 47.38 | 3.33 | 111.30 | ||||||||||||
1041 | BARNES, TIMOTHY | 9/10/2014 | 60.19 | 3.33 | 68.15 | ||||||||||||
4378 | BERRY, CHARLENE | 6/12/2017 | 0.00 | 3.33 | 40 | 43.33 | |||||||||||
3041 | BREWTON, BRIAN | 9/1/2016 | 0.00 | 3.33 | 80.65 | ||||||||||||
90 | BROWN, SHERRIE | 3/11/1991 | 207.75 | 6.67 | 40 | 164.33 | |||||||||||
3102 | COLVIN, KIRK | 2/22/2010 | 91.04 | 3.33 | 67.00 | ||||||||||||
1110 | CRUM, ROGER | 12/8/2009 | 13.90 | 3.33 | 13.86 | ||||||||||||
1175 | ESPARZA JR, ALFONSO | 9/22/2014 | 22.06 | 3.33 | 14.02 | ||||||||||||
3942 | MAYER, CHARLES | 7/9/2012 | 22.44 | 3.33 | 40 | 46.00 | |||||||||||
3441 | MCGEE, RICHARD | 5/2/2016 | 0.00 | 3.33 | 40 | 64.65 | |||||||||||
3054 | MIX, KATHY | 5/9/2005 | 48.49 | 3.33 | 40 | 80.45 | |||||||||||
2530 | NORBECK, MARTIN | 11/1/2005 | 259.87 | 3.33 | 285.08 | ||||||||||||
2606 | PRICE, FELICIA | 2/17/2015 | 127.74 | 3.33 | 168.70 | ||||||||||||
1618 | PUENTES, JEREMY | 5/9/2007 | 44.69 | 3.33 | 40 | 3.40 | |||||||||||
2673 | SAO, LALOTEA | 2/16/2015 | 14.87 | 3.33 | 14.83 | ||||||||||||
2687 | SCOGGINS, LARRY | 11/1/2015 | 113.56 | 3.33 | 129.52 | ||||||||||||
300 | SOLAN, THOMAS | 2/3/2014 | 171.04 | 3.33 | 171.00 | ||||||||||||
1720 | SPRADLEY, DAVID | 1/9/2017 | 0.00 | 3.33 | 76.68 | ||||||||||||
1750 | TORRES, PAUL | 9/1/2013 | 8.94 | 3.33 | 24.80 | ||||||||||||
2809 | TURNER, JACK | 12/27/2016 | 0.00 | 3.33 | 83.35 | ||||||||||||
1786 | VENEGAS, JESUS | 6/1/2014 | 15.13 | 3.33 | 40 | 17.59 | |||||||||||
127 | WATER, ROBERT | 11/17/2003 | 107.74 | 3.33 | 187.66 | ||||||||||||
<tbody>
</tbody>