3 Replies Latest reply on Oct 3, 2019 6:13 AM by Alan E

# Time Duration when Aggregating Across Multiple Fields

Hello,

I am having difficulty aggregating time duration across several calculated fields. I am trying to create a field called "Phone Assistance Time," which is dependent on 4 other calculated fields in the workbook. I've done quite a bit of research on this, and have used the technique recommended in the forums and used by Jonathan Drummey found here:

//replace [Seconds] with whatever field has the number of seconds in it

//and use a custom number format of 00:00:00 (drop the first 0 to get rid of leading 0's for hours)

IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds

+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes

+ INT([Seconds]/3600) * 10000 //hour

I've also modified it to account for SUM([Seconds]) as noted on the blog.

The formula works when viewing the fields independently, however when they are summed together for the "Phone Assistance Time" field (which will be displayed on the dashboard), I get values like 01:84:22 which doesn't really make sense since I want it to be hh:mm:ss. The grand total is not displaying correctly either.

Note I cannot use the /86400 technique as the total duration will exceed 24 hours.

The original data source for this file are two custom SQL statements that I have blended together, with LOGIN and EST as the join keys. However in this example I've just used two Excel spreadsheets to mimic the data so I can attach as a packaged workbook that is accessible.

Thank you! Jonathan Drummey

• ###### 1. Re: Time Duration when Aggregating Across Multiple Fields

You should apply the formatting after the sum. Your Phone Assistance Time formula would then become

SUM([Database2].[Ready Time])+SUM([Hold Time])+SUM([After Call Work])+SUM([ACD Time])

And then create another expression to format that

IIF([Phone Assistance Time] % 60 ==60,0,[Phone Assistance Time] % 60)// seconds

+IIF(INT([Phone Assistance Time]/60) %60 == 60, 0, INT([Phone Assistance Time]/60) %60) * 100 //minutes

+INT([Phone Assistance Time]/3600) *10000 //hours

1 of 1 people found this helpful
• ###### 2. Re: Time Duration when Aggregating Across Multiple Fields

Thank you! This worked perfectly.

• ###### 3. Re: Time Duration when Aggregating Across Multiple Fields

I have a similar question. I am trying to show the number of tickets that have been open for less than 8 hours, less than 24 hours, between 24-72 hours and then anything more than 72 hours. I have a field for time elapsed in hours but my number of records is not adding up. Any thoughts?

I have been using this formula:

IF  [Time Elapsed (Hours)] <8 THEN "0 to 8"

END

IF  [Time Elapsed (Hours)] >24 THEN "24 to 72"

END

I then would change this to a count field.

Thanks!