Adding time

[Fortune]

I have a table where the time is to be added for the week. This poses
no problem. But I have difficulty when there is a night shift. I have
given below an example

Start Finish Start Finish Start Finish Start Finish
Start Finish Start Finish Start Finish
Mon Tue Wed
Thurs Fri Sat Sun

Sup 1 09:00 17:00 09:00 17:00 09:00 17:00 09:00 17:00 09:00
17:00 off off
Sup 2 19:00 7:00 19:00 07:00 19:00 07:00 off
off off off

As can be seen from the above example, supervisor 1 works for 40 hours
a week, and supervisor 2 works for 36 hours a week.

I am looking for a single formula that will deduct 17:00-09:00 for each
day which will give a row total of 40 hours. But at the same time it
needs to be applied to the second supervisor who works the night shift
and completes at 7 am in the morning. This is creating difficulty as
the 07:00 is less than 19:00 the previous day.

Any ideas out there.

 

[Guest]

If start time is A2, Finish time is B2 then
=B2+IF(A2>B2,1,0)-A2

Regards,
Stefi

„Fortune†ezt írta:

 

[Bob Phillips]

=MOD(C3-B3,1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Roger Govier]

Hi

Instead of taking one time from the other as in =B1-A1 use
=MOD(B1-A1,1)
That will deal with both cases.

 

[Guest]

It's really clever!
Stefi


„Bob Phillips†ezt írta:

 

[Guest]

.....or

=B1-A1+(A1>B1)

Hi

Instead of taking one time from the other as in =B1-A1 use
=MOD(B1-A1,1)
That will deal with both cases.