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

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

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

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"z_insyd" wrote in message
...
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

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

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

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

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

"Ragdyer" wrote:

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"z_insyd" wrote in message
...
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

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

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

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

I GOT IT!!!


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

Thanks again for ur help!!

"z_insyd" wrote:

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