Sumifs and Sumproduct Help on Timesheets or other Formulas to solve problem

I am trying to work on a time sheet for my company that both Full Time and Part Time workers can use.


So far on the sheet I have it doing everything that I want it to do, except for one thing.

I need the sheet to calculate overtime better.

At my company you do not hit overtime until after you have worked 40 hours in 1 week. And you have to work 40 hours in the week. So, if you take any sick or vacation time that does not count towards overtime.

So I need a formula that calculates overtime correctly on the sheet as is.

And it cannot be as simple as just subtracting the sick time from the overtime hours to make regular hours. Because one could get sick time one week and work only 40 hours, but the next week that person could actually work a couple hours of overtime and no sick time. Therefore with the simple subtraction the person would not get the overtime hours because the formula is reading the sick time taken from the previous week.

Can someone help me figure out this formula.

I have attached the spreadsheet to this post.