Excel Function Challenge

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I've spent hours trying to create a spreadsheet that will
compare two methods of paying overtime. The logic needed
for the ot rules is just too complicated for me to figure
out. I've been using the "If" function in my attempts. I
thought I'd post the challenge and see if anyone wants to
take a crack at it or give me some pointers.

POLICY #1
The first OT policy is that we pay ot after 8 hours in
any day and after 40 regular hours in one 7 day week.
Weekends are not OT unless the employee already has 40
regular booked.

POLICY #2
The second policy is that OT is only paid after an
employee has worked 40 total hours in the week. Also in
this policy all weekend work is paid at OT rate
regardless of how many hours have been booked.

The week runs Monday through Sunday in both policies. An
employee could work as many as 24 hours in one day with
either policy.
 
Kurt said:
POLICY #1
The first OT policy is that we pay ot after 8 hours in
any day and after 40 regular hours in one 7 day week.
Weekends are not OT unless the employee already has 40
regular booked.
Click to expand...

Too ambiguous. Do you mean more than 8 hours worked in one block? Most
governments that care about overtime would be unlikely to consider a shift
from 5:00PM one day to 3:00AM the next day to be 7 hours and 3 hours,
respectively, of straight time rather than 8 hours of straight time and 2
hours of O/T.
POLICY #2
The second policy is that OT is only paid after an
employee has worked 40 total hours in the week. Also in
this policy all weekend work is paid at OT rate
regardless of how many hours have been booked.
Click to expand...

Do weekends start at midnight or some other time on Saturday?
The week runs Monday through Sunday in both policies. An
employee could work as many as 24 hours in one day with
either policy.
Click to expand...

That the week runs Monday through Sunday is almost irrelevant. The key issue
for #2 is when weekends start and end.

And 24 hour days? You work for a teaching hospital looking at your
internship and residency programs?
 
More than 8 hours worked in a 24 hour day midnite to
midnite.

Weekends begin at one minute after midnite Friday and end
Sunday midnite.

The work is emergency refinery repair.
 
Norman: Thank you for the help. I believe the
instructions that your link took me to have a mistake.
Look at the instruction regarding the MIN H2 H4 formula.
Shouldnt that be H2 and I2?
 
No it is not a mistake, the fixed amount of hours that you compare with is
in $H$2 (look at the red 8), I am sure that if you download the file you
will see it
clearer.

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top