I managed to get E1 working using E1=IF((C1-B1)<0,0,B1)
(still not sure how i tweaked it, but it works that what matters)
That was also substituting
C1=MOD(B1-A1,1) that Ragdyer gave to slove problems with having to use
numbers 24

The only problem is trying to get an answer to D1 i'll keep playing with it
unless someone comes up with anything.

Thanks!

"daddylonglegs" wrote:

Try these formulas

in D1

=MAX(0,B1-1)

and in E1

=C1-D1





"z_insyd" wrote:

Im trying to work out a time sheet for work and the problem is that i work
with 24 hr clock and shifts can start at any time over that period and finish
around 8 hrs later...

i need one result in the formula to answer the time worked in one day
<within 24 hr's and another with the next day. This is where it gets
difficult... when

eg1
All in same day
Start time = 09:00 (A1)
Finish time = 17:00 (B1)
Time worked = 8:00 (C1)
-------------------------
Mon - Fri time = 8:00 (D1)
Sat time = 0:00 (E1)

eg 2.
Working same amount of time but past midnight.
Start time = 18:00 (A1)
Finish Time = 26:00 (B1)
Time worked = 8:00 (C1)
-------------------------
Mon - Fri time = 6:00 (D1)
Sat time = 2:00 (E1)

The rough formula i can work out is that
C1=B1-A1
D1=IF(B1<24:00,C1,24:00-A1)
E1=IF(B1<24:00,0,(B1-24:00)

My problem is because im dealing with a time past 24:00 the equations become
invalid. The bit after that where in multiplying the totals by the rate is
fine that works out as x=SUM(D1:D14)*24*100% and x=SUM(E1:E14)*24*150%. that
works perfect. its just the start where all the trouble is.

Can anyone help me?

Z