Summing time intervals

[milefile]

I'm using some data that came from an IVR system. Many of the numbers I am using are time intervals formatted as HH:MM:SS like speed of answer, length of call, etc. I need to sum these numbers and also get them to sum in a pivot table, but they will not. I have tried every format that remotely looks like it might be relevant to what the value represents, but no luck. Only sums to zero. I am not interested in time of day, only the measurement of length of time using HH:MM:SS. What can I do to these numbers to get them to add up?

 

[inactiveuserps07]

@milefile, the values are likely coming in as text. Try opening a helper column beside them and seeing if this formula provides you with workable number (I'm assuming your original data starts in A2 here, so this would go into B2 and be drag-copied down as fara s needed):

=INT(VALUE(LEFT(A1,2))/24)+TIMEVALUE(A1)

Format the new column as Time > 37:30:55

 

[milefile]

@milefile, the values are likely coming in as text. Try opening a helper column beside them and seeing if this formula provides you with workable number (I'm assuming your original data starts in A2 here, so this would go into B2 and be drag-copied down as fara s needed):

=INT(VALUE(LEFT(A1,2))/24)+TIMEVALUE(A1)

Format the new column as Time > 37:30:55

YES! Thank you so much. Worked and makes perfect sense.

 

[inactiveuserps07]

Good to know on both accounts. Happy to have helped.