I have a very involved spreadsheet to track part time employee sick pay hours. Employees accrue 1 hour of sick time for every 30 hours worked, and the amount of hours they work vary by week. The amount of hours allowed to accrue in a calendar year is 40. I also have to track the amount of hours they have used.
I need to figure out how to cap the amount of hours in "Available Hours" at 40. Right now it just shows the total they've accrued based on hours worked. I had a simple 40-M2 (column showing how many hours have been used) for anyone who reached over 40 hours, however I need something more accruate. Once they use hours and are below 40 again, they should be allowed to continue to accrue until they reach 40 hours again. Is there a formula that can calculate all of this for me?
Bookmarks