formula for hours on time sheet

[Matt]

I have a time sheet that calculates start time and end time, neither of which
could be constant times. That formula works fine.
I am looking to take the total hours for any given day and break them down
into regular hours (upto and including 8 hrs) and overtime pay (anything over
8 hours).

The problem is that some days there might not be a full 8 hrs put in and
then the overtime hours result in an error.

The way I have it set up now is for OT to subtract 8hrs from the Total hrs.
and then the Regular time subtracts the OT hours (or minutes) from the Total
time.

How can I tell the OT formulat to do what I told it to but if the answer is
a negative number to just enter '0'?

Or is there an easier solution???

Can anyone assist me????

 

[Fred Smith]

Simply check to see if the hours worked is greater than 8. Something like:

=if(a2-a1>8,a2-a1-8,0)

Regards
Fred

 

[Matt]

Thanks Fred, but unfortunately that left me with a perminant '0'.

What I have is:
Time in 7:00 (D4)
Time out 15:00 (E4)
Total time 8:00 (F4)
Regular time 8:00 (G4)
Overtime 0:00 (H4)

However sometimes there could be a short day or a longer day

it works fine if the time out is 15:01 or later. Total time is 8:01,
Regular Time
is 8:00, and Overtime is 0:01.

But, when it is a short day, say Time out is 13:00, Total time reads 6:00,
Overtime goes all ####### and Regular time is 0:00.

 

[OssieMac]

The following formula always returns zero if the answer would otherwise be
negative.

=MAX(A1-B1,0)

 

[Fred Smith]

It would help if you told us what formulas you are using. Without them, we
can only guess what's happening. So I'll take a guess that you are using
Excel times for everything. In that case, you need to check for a time
rather than a number. So:

Regular time, G4: =min(f4,time(8,0,0))
Overtime, H4: =if(f4>time(8,0,0),f4-time(8,0,0),0)

Regards,
Fred.