Calculate total activity duration for an Account, Contact or Opportunity
With workflow you can easily calculate the total duration of all completed activities rolled up to a main record such as an Account, Contact or Opportunity. This functionality is built into the service module, but for organizations that track number of hours spent on activities prior to service management (for example, consultants who bill hours prior to the sales transaction) it may be useful to create a custom workflow.
Step 1:
Add a custom attribute field for "Total Activity Duration" to your primary record. In this example I will use the Account record. If you do not know how to add a custom attribute, please view the video on Customizations (start at approx. min 1:30). Because workflow increment calculations only work with number fields, I've made this an "int" type attribute. Add the field to your Account form and publish the changes. In this example I have also added a field "Last Activity Date" to time-stamp the most recent activity for the account.
Step 2:
Create a custom workflow. You will need a separate workflow for each activity type you want to calculate, such as phone calls and appointments - activities for which you want to track total duration. In this example I will use the phone call activity.
Configure your workflow to start running when the activity record is created. Set a wait condition as the first step to wait until the activity has been completed. (If you are forecasting billed hours for the future, you may want to leave out the wait condition and instead set the workflow to update every time the activity duration is updated. It's flexible.)
Update the related Account record attribute that you created in step 1 with "Increment by{Duration(Phone Call)}" in the "Total Activity Duration" field. If you added the attribute "Last Activity Date", update this as well with the actual end date of the activity.
Step 3:
Repeat for the other activities you want to track. Final result:
NB: The increment by feature will calculate duration in terms of hours and then days, which can be cumbersome to report and compare for multiple records. However, when you export the data to Excel it converts all duration values to minutes.
