Adding Times Together

[JeffreyJones]

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.

[MarqueeCrew · Accepted solution]

Jeff,

Nice question.  I didn't know that you couldn't add time values.

I put together a starting solution for you.  It will parse the data, convert to seconds.  Apply math to the seconds to reconstruct Hours, Minutes, Seconds.

It should get you most of the way to your solution.

Thanks,

Mark

[RodL · Accepted solution]

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.  

[JeffreyJones]

Dude, this is outstanding. These two workflows answered all the different questions in the post as well as a few others I didn't even ask*.

Thanks!

*The mod arithmatic function and SubString function both will be making my life easier in the future.

[JBO]

It is frustrating that Alteryx can't perform this basic function without multiple steps, something Excel can do in one step with one formula. I actually think it is easier for me to run the formula in Excel before bringing my data into Alteryx. Alteryx should be able to add time. I spent way too much time troubleshooting this just to learn that I have to run multiple tools and break up my data to achieve it. Very strange.