Need to work out a timesheet with times going into next day (>24 h

[Guest]

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(D114)*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

 

[Ragdyer]

Stop using *fake* times (26:00)!

A1 = 18:00
B1 = 2:00
Formula in C1:
=MOD(B1-A1,1)

 

[Guest]

Try these formulas

in D1

=MAX(0,B1-1)

and in E1

=C1-D1

 

[Guest]

k that could solve a problem in C1 but it doesnt help with D1 or E1

 

[Guest]

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!

 

[Guest]

I GOT IT!!!


D1=IF(B1<24,MOD(B1-A1,1),0)
E1=IF((C1-B1)<0,0,B1)

Thanks again for ur help!!