Sum of duration currently in time of day format

[Explorador]

I have these durations of calls but they currently appear like times of day (12:03:01 AM instead of 12:03:01 as a length of time) and I need to add them all up to have a sum of duration. How can I do this?

 

[JamesCanale]

Are they three or four minute calls?

Just add them up with sum. But format the call length to custom: "m:ss". The sum should probably be formatted like: "h:mm:ss"

 

[Explorador]

They are sums of all calls during a month, so for example in the first cell it's 12 hours, 3 minutes and 1 second. When I try formatting it like you suggested it believes it's 3 minutes 1 second. Any ideas?

 

[JamesCanale]

It's a data entry problem.

MrExcelPlayground2.xlsm
	I
23	12:03:01
24	12:03:45
25	12:04:02
26	12:03:05
27	12:13:53
28	60:27:46
Sheet29

Cell Formulas
Range	Formula
I28	I28	=SUM(I23:I27)

The formatting is the key. Put the monthly entries in 24 hour time. You are getting 12:03 AM, instead of PM. fix it by adding 0.5.

The formatting of the sum should be "[h]:mm:ss"

 

[Explorador]

It's a data entry problem.

MrExcelPlayground2.xlsm
	I
23	12:03:01
24	12:03:45
25	12:04:02
26	12:03:05
27	12:13:53
28	60:27:46
Sheet29

Cell Formulas
Range	Formula
I28	I28	=SUM(I23:I27)

The formatting is the key. Put the monthly entries in 24 hour time. You are getting 12:03 AM, instead of PM. fix it by adding 0.5.

The formatting of the sum should be "[h]:mm:ss"

Hi James,
Can you explain in more depth what you mean by putting it in 24 hour time and adding .5? Like the step by step process of what you did you solve it. Thank you so much!

 

[JamesCanale]

In the data entry, someone is maybe typing "12:03:45", and excel took that to mean 12:03:45 AM. If it's 12 hours and 3 minutes, then you want it to be 12:03:45 PM. But it's in there as AM. If you format the cells in 24 hour time:

then when it's typed in, it will show 0:03:45 if it gets in with an AM. Which would catch everyone's eye as incorrect.

But if you want to fix a bunch of times that are 12:xx:xx AM to the same PM, then literally add 0.5 to each cell - that will add a half a day or 12 hours.

Format the sum cell like this:

 

[Explorador]

Sorry to keep asking questions, but I don't get what you mean by "adding .5", could you explain what the formula for one of the cells with the time in it would look like with the +.5?

Inbound_Marketing_Specialist_-_Test.xlsx
	F
8	Call Length
9	0:03:01
10	0:03:55
11	0:04:09
12	0:03:30
13	0:03:38
14	0:02:10
15	0:03:00
16	0:03:49
17
Reporting test

 

[JamesCanale]

Sure:

MrExcelPlayground2.xlsm
	I	J
23	0:03:01	12:03:01
24	0:03:45	12:03:45
25	0:04:02	12:04:02
26	0:03:05	12:03:05
27	0:13:53	12:13:53
28	0:27:46	60:27:46
Sheet29

Cell Formulas
Range	Formula
J23:J27	J23	=I23+0.5
I28:J28	I28	=SUM(I23:I27)