Microsoft Excel 010 - complete package

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 hrsEmployee 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:

=IF(P45+Q45>40,40-Q45,0)

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?

Thanks! Robyn

Without trying too hard to understand your post, I'll offer this: If the "concept" of =IF ((P45+Q45>40) AND (P45<40), 40-Q45,0) gets you the answer you are looking for, it should be written as:

=IF(AND(P45+Q45>40, P45<40), 40-Q45, 0)

Look up the AND function in the Excel Help files to get a better understanding of how the function is used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03, Thanks for your assistance. However, although your solution works well for when there are fewer than 40 hours actually worked but more than 40 hours including holiday time, it doesn't properly work for employees who worked more than 40 hours (with or without holidays). The answer it gave me ended up being a negative number, which, when added to the normal time-and-a-half hours end up reducing the number of overtime hours that would be paid. Sorry this is such a complicated problem. I don't know how to explain it to make it easier to follow.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History