11 Replies Latest reply on Mar 10, 2017 6:33 AM by Deepak Rai

Aggregating HH:MM:SS

Hi,

I would like to sum/average etc a set of call durations.

For example (hh:mm:ss):-

Day1 00:04:30

Day2 01:59:34

The total should be 2 hours, 4 minutes, 4 seconds

Could someone please point me in the right direction.

Many thanks in advance.

• 1. Re: Aggregating HH:MM:SS
1 of 1 people found this helpful
• 2. Re: Aggregating HH:MM:SS

Thanks for replying Deepak. I shall look at this tomorrow when back in work.

One question... Why use % sign I.e. %3600 %60

Many thanks.

• 3. Re: Aggregating HH:MM:SS
1 of 1 people found this helpful
• 4. Re: Aggregating HH:MM:SS

Hi Deepak,

Can you please explain how you make the following calculations...

[Data - Split 2 - Split 1]

[Data - Split 2 - Split 2]

[Data - Split 2 - Split 3]

Many thanks.

• 5. Re: Aggregating HH:MM:SS

Hi Deepak,

I actually got this to work, so many thanks for your help with this.

Kind regards.

• 6. Re: Aggregating HH:MM:SS

Hi Paolo,

Glad to know that. Firt you split Day1 and Time based upon the blank space, and then you split Time based upon colon(:).

That's it

Thanks

Deepak

• 7. Re: Aggregating HH:MM:SS

Hi Deepak,

Sorry, but can I ask you one further question please.  I would like to get an average of the total amount of hours.

So, for example, I currently have this calculation (which is your example) and works really well by summing all the hours over a period of time:-

STR(INT(SUM([Total Call Duration_SplitConvSeconds])/3600))

+ ":" +

IF INT(SUM([Total Call Duration_SplitConvSeconds])%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT(SUM([Total Call Duration_SplitConvSeconds])%3600/60))

+ ":" +

IF INT(SUM([Total Call Duration_SplitConvSeconds])%3600%60)

<10 THEN "0" ELSE "" END + STR(INT(SUM([Total Call Duration_SplitConvSeconds])%3600%60))

I want to be able to get his figure and divide it by the total number of calls to get an average.

For example:

4:19:01 and divide this by 29 which is the total number of calls.

I have been trying this, but it's not really working:-

STR(INT(SUM(([Total Call Duration_SplitConvSeconds])/[# Chats])/3600)) ... and so on down the calculation, but it doesn't seem to work.

Many thanks.

1 of 1 people found this helpful
• 8. Re: Aggregating HH:MM:SS

Hi Paolo,

• Use the Calculations I sent to you to convert your aggregated time into seconds to work at minimum level.
• Divide those seconds by 29 to get Average number of Seconds and Round them off using ROUND function
• Like if u have 3600 seconds, divide by 29 like this ROUND(3600/29). suppose it is calculation 3
• Now you got average Seconds
• Convert them back toHH:MM:SS using formula I sent u

Hope it Helps!!!

Thanks

Deepak

1 of 1 people found this helpful
• 9. Re: Aggregating HH:MM:SS

Hi Deepak,

I've got this working ..... but not quite!

The graphic on the left works correctly when I manually enter the number 4.  However, if I add the actual field name called

# Chat - which is a whole number data type - (right graphic), I get the "cannot be aggregated" message:- I have tried adding SUM and STR and all sorts, but cannot get this to work.

Apologies for the persistent questions.

Kind regards.

• 10. Re: Aggregating HH:MM:SS

No worries, I now have this working.  I just had to add SUM to make it work:-

SUM(((INT(SPLIT([TOTAL]..........)))/[# Chats]

Then take the SUM away from the second calculation.

Many thanks again.

1 of 1 people found this helpful
• 11. Re: Aggregating HH:MM:SS

Great Work!!!!