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.

Comments

• Anonymous
  December 26, 2008
  With workflow you can easily calculate the total duration of all completed activities rolled up to a

• Anonymous
  July 20, 2010
  Hi Thanks for a good blog guide! I use this on Case. It works with Task and Phone Call, but with E-mail the Workflow is triggered twice. I check on E-mail = Complted and if Status Reason is Sent. But CRM see this record change status twice and therefore fire Duration twice?!?! Any ideas?

• Anonymous
  December 17, 2013
  Just in case it's useful, you can run reports for time at the case level.  Just add "Case Resolutions" to your report at the initial "Report Properties" screen.  So in your report, your primary record type is probably "Case" with related record type of "Case Resolutions (Case)".  When you add a column to the report, choose "Case Resolutions", then underneath select "Time Spent".  This will add a sum of any activity time that was logged in minutes.  Or it will log the manual amount of time entered in the case resolution (if it was more).  

• Anonymous
  August 11, 2014
  I want to total all appointment hours in to the Customer record. Thanx, Scott