Hello, I am stumped on a timecard we are building in our office for our timekeepers. Here are the basic rules:

1. Earn 4,6 or 8 hours Annual leave per pay period (which is every other week) depending on their years of service.
2. Earn 4 hours Sick leave per pay period
3. Take a maximum 80 hours of Leave Without Pay(LWOP) per pay period AND when the balance reaches 80 hours LWOP in any given pay period, their Annual AND Sick leave result in zero hours earned for the pay period.

I can get the Annual and Sick leave to "Zero" out when the LWOP cell reaches the increments of 80 (80,160,240,320, etc.) using this formula:

B4=LWOP taken (if the timekeeper inputs any, then it runs the rest of the formula, else the Annual/Sick leave remains unchanged at 4,6,or8 hours earned)
C4=Running balance of LWOP
D4=Annual or Sick Leave Earned

=IF(ISBLANK(B4),D4,IF(C4=80,0,IF(C4=160,0,IF(C4=240,0,IF(C4=320,0,IF(C4=400,0,IF(C4=480,0,IF(C4=560,0,IF(C4=640,0,IF(C4=720,0,IF(C4=800,0,IF(C4=880,0,IF(C4=960,0,IF(C4=1040,0,IF(C4=1120,0,IF(C4=1200,0,IF(C4=1280,0,IF(C4=1360,0,IF(C4=1440,0,IF(C4=1520,0,IF(C4=1600,0,IF(C4=1680,0,IF(C4=1760,0,IF(C4=1840,0,IF(C4=1920,0,IF(C4=2000,0,IF(C4=2080,0,IF(C4=2160,0,D4))))))))))))))))))))))))))))

This works great, HOWEVER, IF the employee takes 1 hour one pay period and then 75 the 2nd pay period their running balance of LWOP 76, which is under the threshold of 80 hour increments and therefore does not change the annual/sick leave earned. Which is correct. But then on the next pay period, if the employee takes 8 hours LWOP, it pushes them over the 80 hour threshold and should zero the earned annual/sick out.

I cannot figure out how to make it zero out when it hits 80 hours every time, even though the first time could be 86 hours then the next one 165, those both should result in zero hours earned for annual/sick time.

I attached a sample of the formula above in a spreadhseet.

Thank you for your time!
Sample Timecard Help Needed.xlsx