# Calculating duration sum hh:mm:ss

#### chowell51

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### jasonb75

##### Well-known Member
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

##### New Member
here is an example

#### Attachments

• Screen Shot 2020-11-06 at 9.37.24 AM.png
89.4 KB · Views: 12

#### sandy666

##### Banned - Rules violations
with data copied from your post
C
202:45:11
303:44:47
46:29:58
Sheet1
Cell Formulas
RangeFormula
C4C4=SUM(C2:C3)

#### chowell51

##### New Member

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.

#### chowell51

##### New Member
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.

Replies
3
Views
97
Replies
9
Views
246
Replies
9
Views
222
Replies
8
Views
292
Replies
3
Views
35

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back