How to calculate hours?

[Guest]

I have a stop and start time in two side by side cells. I need the hrs
worked in the next cell but the variouse functions I have tried havn't
worked. I usually get 0:00.
I'm using the custom formatting hh:mm. I have two twelve hours shifts,
24/7. My results look like this: Start ----- Stop ------
Total hrs worked
(day shift) 6:00 AM 6:00 PM 0:00
(night shift) 6:00 PM 6:00 AM 0:00
(occasional odd ones) 8:00 PM 2:00 AM 0:00

The start/stop entries are be entered in military time (18:00 = 6:00 PM). I
need the total hrs worked to calculate automatically. Can someone please
help?

 

[Peo Sjoblom]

=MOD(End_Time - Start_Time,1)

so if B2 holds end time and A2 start time

=MOD(B2-A2,1)

if you total the hours later use custom format as in [hh]:mm or else the
display will start over again after 24 hours

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Govind]

Hi,

Use

=IF(B1<A1,B1+1-A1,B1-A1)

where B1 is the stop time and A1 is the start time.Format the result in
hour format.

Regards

Govind.

 

[Guest]

Thanks so much. Now each persons hours are calculating correctly. But now
the crews total hours are not adding up correctly at the bottom of the
column. Does anyone know why or how to format or fix this cell?

 

[Guest]

You should be able to just subtract the begin time from the ending time, but
you may need to enter the date along with the time (to accomodate shifts that
span different days).

For example:
A B C
1 7/31/05 6:00 7/31/05 18:00 =B1-A1

 

[Govind]

Hi,

The total should be a normal sum formula but the cell should be
formatted in hour format.

Regards

Govind.

 

[Roger Govier]

Hi Cheryl
As Peo told you in his response, your total cell need to be custom formatted
to [hh]:mm to allow a rollover past 24 hours