Calculate the sum of a Time formate column

  • Thread starter Thread starter Nick Malone
  • Start date Start date
N

Nick Malone

I have a column of data in the time format.
Example:

00:25:10
00:00:05
01:55:20


I am looking for an equaltion that would add the total of
the column and leave it in the same format.
 
Sum the column (for example, =SUM(A:A) ) and custom format
the formula cell as [hh]:mm:ss.

HTH
Jason
Atlanta, GA
 
formula =SUM(G1:G781) custom format =hh:mm:ss

The problem is this is note a time like 12:00:13 AM

it is a duration of time 12 hours, 0 min. and 13 seconds.

Sorry I didn't do a good job of explaining that. When I
perform the sum equation i get all 00:00:00's

Any suggestions?

-----Original Message-----
Sum the column (for example, =SUM(A:A) ) and custom format
the formula cell as [hh]:mm:ss.

HTH
Jason
Atlanta, GA
-----Original Message-----
I have a column of data in the time format.
Example:

00:25:10
00:00:05
01:55:20


I am looking for an equaltion that would add the total of
the column and leave it in the same format.
.
Click to expand...
.
Click to expand...
 
formula =SUM(G1:G781) custom format =hh:mm:ss

The problem is this is note a time like 12:00:13 AM

it is a duration of time 12 hours, 0 min. and 13 seconds.

Sorry I didn't do a good job of explaining that. When I
perform the sum equation i get all 00:00:00's
Click to expand...

First of all, you should have brackets around the h's or you will not be able
to display a time > 24 hrs. So the custom format should be [hh]:mm:ss

Second -- if your SUM is resulting in 0's, then the data is stored as text and
not as an excel recognized time.

It would probably be simplest to convert the data to time numbers. To do that
select a blank cell; then Edit/Copy.

Then select your range of time data.
Edit/Paste Special Operation:= Add

This will likely convert your data into small numbers -- e.g. .00128; .0125.

Format the range as hh:mm:ss and you should see your original data.

The SUM should then work.

If you do not wish to alter the original data, try the *array-entered* formula
=SUM(TIMEVALUE(G1:G781)).

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>


--ron
 
Back
Top