So I've got this big file of telephone call times and service agents. What I want to do is summarize the total time spent on the phone by each agent (though that isn't necessary for my question). The times are all in the format HH:MM:SS. For example, if Agent X is on a call that lasts twenty seven minutes and thirty three seconds, the time field is 00:27:33. His next call might last two minutes and fourteen seconds and it will show up in the log as 00:02:14.
QUESTION: How do I add two times in that HH:MM:SS format together for a combined total time?
In that example, 00:27:33 + 00:02:14 should = 00:29:47
My first idea is to simply parse the time string into hours, minutes, and seconds. Then do the appropriate math to convert everything to seconds (an Int32) and then use the summarize tool to aggregate total seconds spent on all calls by each agent. Under this example, I'd worry about the conversion back to HH:MM:SS format later, and we have actually have a calculated field on the Tableau side that does that for us already.
Is there a better way to solve this problem than the above? If not, what's the best way to parse that string? RegEx? I'd rather solve it within a single formula tool if possible, but if I need to break those into three new columns using the text-to-columns tool before doing the math, then that's the way I'll go. To add another layer to this question, I need to do this for several different call time fields (wait time, post-processing time, break time, etc.) so maybe this is an appropriate case for a macro? I'm still fairly new and don't have any experience with macros, so maybe it's time to learn.
Anyway, thanks for the guidance and for the awesome product.
