Adding time values

D

Dodge SRT-4

Hello all, First time posting and I hope I have this in the right forum
sorry if I don't.

Here is my situation: I have numerious machines that give me hous o
operation in the following format hh:mm:ss.

In my 2nd column is the time of each machine operation run. I have al
the cells formatted 00":"00":"00 so that they display in the hh:mm:s
when I just type in just th six numbers i.e. if I type in 032536 i
displays 03:25:36 .

Now my question is how can I add the numbers up to reflect tota
machine operation run times?

Example of current spread sheet:

Machine # Operation Time

1 02:25:36
2 01:45:30
3 03:16:09

Total Time 06:86:75


I need the time to reflect proper time i.e. 07:26:15

Your help is greatly appreciated
 
M

macropod

Hi Dodge,

You could use an array formula (i.e. press Ctrl-Shift-Enter to confirm, not
just Enter) like:
=(SUM(MOD(A1:A3,100))+SUM(MOD(INT(A1:A3/100),100))*60+SUM(MOD(INT(A1:A3/1000
0),100))*3600)/86400
and format the result as:
[hh]:mm:ss

Alternatively, if you need to retain you current numeric format, you could
use an array formula like:
=(SUM(MOD(INT(A1:A3/10000),100))+INT(SUM(MOD(INT(A1:A3/100),100)+INT(SUM(MOD
(A1:A3,100))/60))/60))*10000+MOD(SUM(MOD(A1:A3,100)),60)+MOD(INT(SUM(MOD(A1:
A3,100))/60)+SUM(MOD(INT(A1:A3/100),100)),60)*100
and format the result with your existing custom format.

Naturally, you'll need to change the cell ranges to suit your data.

Cheers
 
J

Jerry W. Lewis

Excel has time formats (h:mm:ss, hh:mm:ss, [h]:mm:ss and [hh]:mm:ss)
that are based on decimal fractions of 24 hours. If you had used that,
then you could have simply added the times.

As it is, you will have to do a lot of work to split the "times" appart,
add hour, minutes, and seconds separately, handle carries into the next
unit, and put it all back together again into a final result.

To avoid errors, you should probably breaking the times appart using
formatted text, as in
=VALUE(LEFT(TEXT(C1,"00"":""00"":""00"),2))
to get the hours

Jerry
 
Top