adding military time columns

[Guest]

Trying to find the total time of duties performed using military time. Example:
Firefighter A puts his start time in column A4 ( 08:00) , his ending time in
column B4 ( 16:00 ) , I would like column C4 to display the result. ( 8
hours)
If I try ( IN C4) =a4-b4, I get a negative result which shows #########.
Any help or suggestions would be appreciated.

 

[Niek Otten]

To learn all about calculating with times, visit

http://www.cpearson.com/excel/datetime.htm#AddingTimes

--

Kind Regards, to your dogs, too

Niek Otten

Microsoft MVP - Excel

 

[Peo Sjoblom]

If you use 08:00 and 16:00 that is not military time, it's just regular 24
hour time. I believe military time would be
800 and 1600, nevertheless unless there is a hidden date in the cell with
08:00 that is later than what's in the cell with 16:00 it should work OK.
Are you sure you just don't have enough "room" for the result to display?
Try with a new sheet and put 08:00 in A4 and 16:00 in B4, that should return
08:00, if you want 8 you have to multiply with 24 and format as general

Regards,

Peo Sjoblom

 

[Harald Staff]

Hi

You're subtracting end time from start time. Do the opposite:
=B1-A1
and expand it to
=B1-A1+(B1<A1)
to deal with eventual end times past midnight.

HTH. Best wishes Harald

 

[Peo Sjoblom]

Doh! <g>

Peo Sjoblom

Hi

You're subtracting end time from start time. Do the opposite:
=B1-A1
and expand it to
=B1-A1+(B1<A1)
to deal with eventual end times past midnight.

HTH. Best wishes Harald

time

 

[Myrna Larson]

YOu have to subtract the start time from the end time, not vice versa (which
is what you are now doing). The formula should be =B4-A4

 

[Guest]

Thank You , this worked beautifully.

Hi

You're subtracting end time from start time. Do the opposite:
=B1-A1
and expand it to
=B1-A1+(B1<A1)
to deal with eventual end times past midnight.

HTH. Best wishes Harald