Time and a half

[bnwash]

I have attached an example of a worksheet which I have been developing for the purpose of assisting in getting a weekly payroll right.
The basis is
Any hours over 8 and up to 10 monday to Fri are time and a half - any hours after 10 are at double monday to fri
on Sat the fist 2 hours are time and a half the rest are at double
Sunday - all hours at double

The existing code in 32 and 33 work wonderfully well providing that work happens each day monday to fri but if your look at the Row f32 you can see the problem. the code puts the figure into negative.
Because he was sick on one day and had an RDO on the other - the result is -14 - this should show 2 [because of the 2hours on Saturday]

Any suggestions on how to fix this please

[Winon]

I don't see your "problem"

[JBeaucaire]

In F2, try this array formula:

=IF(SUM(F10:F14)=0, 0, SUM(IF(F10:F14-8>2,2,IF(F10:F14-8<0,0,F10:F14-8))))+SUM(IF(F15:F16>2,2,F15:F16))

[bnwash]

Many thanks that works really well

[JBeaucaire]

If that takes care of your posted question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[bnwash]

Thanks for the help
But I still have the "#Value" problem when there is no data in rows 10-16
Sometime the people will not work any hours that week

Help appreciated
l

[JBeaucaire]

Your original formula has a text string in it, remove that. Replace it with a 0 instead. I've edited post #3 and highlighted that change in red.

[JBeaucaire]

Actually, you don't even really need that part. The simpler array formula would be:

=SUM(IF(F10:F14-8>2,2,IF(F10:F14-8<0,0,F10:F14-8)))+SUM(IF(F15:F16>2,2,F15:F16))

[bnwash]

Appreciate your assistance
Many thanks

[JBeaucaire]

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.