Calculating duration sum hh:mm:ss

[chowell51]

I am trying to get a sum of time spent on the phone for agents. The format the duration is in is hh:mm:ss when I format the column and do =C2+C3 I get the correct output. When I do =SUM(C2:C55) the sum is 00:00:00. I've tried a few different ways to format and calculate the sum without luck.

Example in (hh:mm:ss)

C1= Charles talk time each day
C2 = 02:45:11
C3 = 03:44:47
=C2+C3 output is 06:29:58
=SUM(C2:C3) output is 00:00:00

 

[jasonb75]

If the data starts off as times in text format then changing the cell format will not automatically make it a valid time. The fact that it works with C2+C3 but not with SUM() tells me that this is the case with your data.

The easiest way to fix it is with text to columns. Select the cells with times (C2 and C3) then go to the Data tab on the excel ribbon.
Click Text to Columns
In the popup window, click Next then uncheck all boxes.
Click Next again, then click Finish.

The SUM() formula should now work.

 

[chowell51]

here is an example

 

[sandy666]

with data copied from your post

	C
2	02:45:11
3	03:44:47
4	6:29:58
Sheet1

Cell Formulas
Range	Formula
C4	C4	=SUM(C2:C3)

 

[chowell51]

If the data starts off as times in text format then changing the cell format will not automatically make it a valid time. The fact that it works with C2+C3 but not with SUM() tells me that this is the case with your data.

The easiest way to fix it is with text to columns. Select the cells with times (C2 and C3) then go to the Data tab on the excel ribbon.
Click Text to Columns
In the popup window, click Next then uncheck all boxes.
Click Next again, then click Finish.

The SUM() formula should now work.

Ok I'll try that right now.

 

[jasonb75]

See previous reply.

 

[chowell51]

If the data starts off as times in text format then changing the cell format will not automatically make it a valid time. The fact that it works with C2+C3 but not with SUM() tells me that this is the case with your data.

The easiest way to fix it is with text to columns. Select the cells with times (C2 and C3) then go to the Data tab on the excel ribbon.
Click Text to Columns
In the popup window, click Next then uncheck all boxes.
Click Next again, then click Finish.

The SUM() formula should now work.

It worked, you are the man!! This is the street wise excel knowledge they don't teach you in a tutorial.