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

Z

z_insyd

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
 
Z

z_insyd

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!
 
Z

z_insyd

I GOT IT!!!


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

Thanks again for ur help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top