Calculating how many business hours an issue is open

[William__Bill_H]

Hello,

We have a legal review process in Workfront and we would like to determine the average number of business hours to complete the request.

In the past 3 months, we have run hundreds of legal reviews in Workfront. An issue is generated in a queue, a document that needs to be reviewed is attached, and the request is routed to our Legal Department. Once the review is complete, the issue status is changed to a custom status of Legal Review Complete (LRC), which equates to Closed. We would like to track a monthly average of how long these reviews are taking, but to do so, we need to exclude evening hours, weekends, and holidays.

For instance, we can run a Journal report and determine the request Entry Date and Status LRC (custom code determining the date/time when the status is changed to LRC) date; following is a recent example:

Entry Date: 12/15/22 4:52 PM
Status LRC: 12/20/22 11:58 AM

If we run a time difference calculation, we get 115 hours and 6 minutes. However, that’s not correct because we’re not limiting the review to established business hours. The real answer is 16 hours and 6 minutes (If we take into account that our regular business hours are 9 to 5, our company offices close at 1:00 PM on Fridays, and the request spanned a weekend).

Is it possible to set up a calculation that would take our default schedule into account and give us the correct values? Any direction would be appreciated!

Thanks,

Bill Harnett

[Richard_Le_ · Accepted solution]

Hi Bill,

This will be achievable using the following calculation in text mode:

WORKMINUTESDIFF({entryDate},{actualCompletionDate})/60

This calculation will return the number of working minutes between when the request was raised and when it was closed, and then divide that value by 60 to give the number of working hours. The calculation will be made against the default schedule of your Workfront instance, so as long as the schedule reflects your normal business hours it will accommodate the early office closure on a Friday and ignore weekends.

Best Regards,

Rich.