|Trying to figure out timesheets for payroll and one calculation is messing me up.|
Employee worked 38 hrs
Paid Holiday for: 8 hrs
Total week's Hrs: 46 hrs
Employee gets 6 hours of comp time (or overtime) but does not get them at the 1-1/2 rate because he did not work a full 40 hours. I need a formula that returns 6 hours in my 1:1 comp cell instead of 9 hours in my 1:1.5 comp cell. I have the 1:1.5 comp cell figured out, but I can't seem to get a formula for the 1:1 comp cell that returns a value other than "0" ONLY when the actual worked hours are less than 40. What I originally came up with was:
This works great when the actual worked hours are less than 40. However, when the actual hours worked are greater than 40 I end up getting that number PLUS I get the 1:1.5 calculation in the other comp earned cell and this gives too many total comp hours earned.
I feel like I want a formula that states
=IF ((P45+Q45>40) AND (P45<40), 40-Q45,0)
This doesn't work, but is there a way to make something like this work?