Adding Durations of Time in Excel 2

[jsisley]

Sorry if this has been posted before, I looked through this forum and didn't see this. I have a column of time durations (i.e. each row representing the length of long distance calls made from a particular phone number over the course of a month). How do I add this column to get a total long distance call duration for the month? Some of these figures are quite large (i.e., 172:48:0). The goal is to chart the monthly call durations. Any help or ideas are appreciated. Thanks!

JoAnn

 

[AnotherHiggins]

If you are dealing with times, add them together. The key is to be sure to format the cells correctly.

Format > Cells > Number > Custom > [h]:mm:ss

By the way, if you search for "add times"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jsisley]

Thanks! This does work - in most cases. I do have some numbers that are quite large (30,167:06:00). I've removed the comma, made sure that the formatting of the cell is as you described above, but this particular cell is still not being included as part of the overall calculation (the cell itself is included in the range of cells being added). What else do I need to do to get this number included?

JoAnn

 

[GlennUK]

Please explain what you think this number represents. If it's supposed to be 30167 hours, that's approximately 1257 days ... are you sure that's right?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

JoAnn,

The actual VALUE for 30,167 hours 06 minutes is 1256.9625.

It FORMATS as advertised...
[tt]
30167:06
[/tt]
faq68-5827

Skip,

_[red][/red]

 

[onedtent]

anytime hours entered exceed 9999, the number entered is treated as text string. to change it use =30167/24+"00:06:00" and the cell itself formatted to [h]:mm:ss

or you could convert the hours to days and hours, add and switch days back to hours +

 

[jsisley]

OK... I started looking at these figures and they seemed impossible.. Thanks GlennUK for bringing this up. I'm tracking call center outbound call volume, so the large numbers at first seemed OK. After I looked at it more, it didn't seem right. I'm getting the data from the vendor-provided CD. I found out that it is formatted as minutes:seconds:milliseconds.

Do I still format these as [h]:mm:ss or should I use another format in order to add these? I guess I'd prefer the final answer to be formatted as minutes:seconds:milliseconds.

I really appreciate everyone's help on this one!

 

[SkipVought]

Convert ALL to DAYS. Forget the milliseconds.

The FORMAT as previously directed. It's that simple.

Skip,

_[red][/red]

 

[onedtent]

my head hurts after this, but how does this work for u?

=LEFT(LEFT(A2,LEN(A2)-3),LEN(LEFT(A2,LEN(A2)-3))-3)/1440+SECOND(RIGHT(LEFT(A2,LEN(A2)-3),2)*1)

it will convert it to hours, mins & secs. ignores millisecs

 

[SkipVought]

onedtent,

Why are you messing with STRING FUNCTIONS?

This is Date/Time. The underlying VALUES are NUMBERS.

Its just arithmetic!

Skip,

_[red][/red]

 

[onedtent]

kind sir,

since there are mins, secs & millisecs involved, excel would only see it as hms. is there a good way to ignore millisecs?

 

[SkipVought]

"...it will convert it to hours, mins & secs. ignores millisecs..."

I don't get your question. Excel does not have the precision in Date/Time values to display millliseconds.

However, the milliseconds can be converted to days and summed for the period, and could result in additional minutes or seconds.

The point is, ITS ALL MATH not string manipulation.

Skip,

_[red][/red]

 

[mintjulep]

What is on the "vendor-provided CD"? Is it .xls files, or something else?

Skip,

It may actually be a string, or it may be being ported into Excel as a string. The delimiter between seconds and milliseconds should not be a ":", so Excel may get confused if you start applying time formatting to a string that looks like a valid time, but really isn't, or more correctly may produce unexpected results.

 

[jsisley]

This is a CD we get from our long distance provider every month that shows call detail - date/time/duration/cost of each long distance call that is made. This info was exported from the CD to Excel. I can get most of the numbers to add up, with exception of the ones that are over 1000 minutes.

 

[SkipVought]

That's why I stated...

Convert ALL to DAYS. Forget the milliseconds.

The FORMAT as previously directed. It's that simple.

Convert the millisecs, alse, and forget DISPLAYING millisecs, as Excel cannot display to that precision.

Skip,

_[red][/red]

 

[mintjulep]

Ok, so the question then becomes:

How do you convert (what is most likely) a string of "minutes:seconds:milliseconds" into days?

Which at first look certainly appears to require some string functions to parse out the numeric chunks.

There might be a better way if we get an answer to my "What is on the CD?" question.

 

[onedtent]

all i can do is suggest

what you need is

=If(ISTEXT(A2),LEFT(LEFT(A2,LEN(A2)-3),LEN(LEFT(A2,LEN(A2)-3))-3)/1440+SECOND(RIGHT(LEFT(A2,LEN(A2)-3),2)*1),A2*0.0166666666)

this will give you hours,mins, sec. don't forget to format to [h]:mm:ss

i know strings are a no, no..... but this is one of those things where you need 'em

 

[SkipVought]

I managed to do this in about 3 minutes...

1) Use Data > Text to columns... DELIMITED on [COLON] - this results in three columns for Minutes, Seconds and milliseconds

2) Minutes to DAYS...
[tt]
B2: =A2/24/60
[/tt]
3) Seconds to DAYS...
[tt]
C2: =A2/24/60/60
[/tt]
3) Milliseconds to DAYS...
[tt]
D2: =A2/24/60/60/1000
[/tt]
4) Sum all
[tt]
E2: =SUM(B22) and FORMAT as [h]:mm:ss
[/tt]





Skip,

_[red][/red]

 

[onedtent]

skip's suggestion is good. if you use mine the original formula is better

 

[mintjulep]

I always forget about that text to columns feature. I was thinking along the same lines as part of the CD -> Excel import.