This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
Yeah, I'm with @MarqueeCrew and didn't know you could just sum time.
I've added my version...looks like @MarqueeCrew's math is simpler, but I wanted to address your comment that you had multiple fields to convert. The attached shows the effect of putting the expression all together so you could make use of the Multi-Field Formula tool. With that tool, if you have a single expression, you can then apply that same logic across multiple Time fields with.
The second Multi-Field Formula tool does the conversion back to a time string.
You can always take his math and the Multi-Field process and combine them. :smileyhappy: