Tasks | How do I Report on Days to Completion

Abstract

Ever try to report on how many days typically pass between the date Tasks are created and the date they’re closed?

Out of the box, it’s kinda tough to do. Creating a Report that shows an average, min, max etc. value across Records is pretty simple. So long as the data is stored on the Records, you’re good to go. Herein lies the problem… Out of the box, there’s no place to store the date a Task is completed and even if there was, are you really going to ask your End Users to update another field every time they complete a Task?

Thankfully the solution for this one is pretty straight forward.  Its primary components are:

• Custom Fields
• A Workflow Rule and
• A Workflow Action

If you’re not familiar with one of the topics listed above, click on it to be brought to a Trailhead Unit that’ll get you up to speed.

Create a Place for Your Close Date

The metric you’re looking to report on is effectively the difference between two dates. The date on which a Task was created and the date on which it was marked as “Completed”. While Salesforce was kind enough to give us the first one, we’ll have to make a home for the second. Here’s how:

Click Path 

Setup | Customize | Activities | Activity Custom Fields | New

1. Data Type: Date
2. Field Label: Close Date
3. Grant Visibility as you’d like, but make this field Read Only to all but the System Admin
4. Add this field to all desired Page Layouts
5. Click “Save and New”

After creating the field that will hold the Close Date, let’s build a Formula field that will use it.

1. Data Type: Formula
2. Field Lable: Days to Completion
3. Return Type: Number
4. Number of Decimal Places: 0
5. Formula:

   1. Closed_Date__c - DATEVALUE(CreatedDate)

Note: Like most systems, SFDC looks at dates as numbers going back to January 1st of the year 1900. Every day equals 1. This is the reason why if you change the formatting of a date cell in Excel to “Number”, a string of numbers is shown. i.e. 01/01/2000 = 36,526. January 1st of 2000 was 36,526 days after 01/01/1900.

Now that we have places for all the information, we need to populate the “Close Date” field automatically. For this we’ll use a simple Workflow Rule (WFR) and Action (WFA)

Auto-Populate the Close Date Field

Now that you have your fields in place, let’s get the “Close Date” to auto populate whenever a Task’s Status is set to “Completed”.

Click Path 

Setup | Build | Create | Workflow & Approvals | Workflow Rules | New Rule

1. Select “Task” as your Object
   1. 
2. Click “Next”
3. Name your WFR something like “Task | Status = Completed”
4. Enter a brief description of your WFR
5. Evaluate the rule when a Record is: created, and any time it’s edited to subsequently meet criteria
6. Run this rule if the following: criteria are met
   1. Field Name: Task: Status
   2. Operator: Equals
   3. Value: Completed
7. If your WFR configuration page looks like the one below, Click “Save and Next”
   1. 
8. From the “Add Workflow Action” drop down in the “Immediate Workflow Actions” section, select “New Field Update”
   1. 
9. Name your new Field Update something like “Populate Closed Date”
10. Field to Update: Close Date
11. Date Options: Use a formula to set the new value
    1. Formula:

       1. TODAY()

12. Click “Save”
13. Click “Done”
14. Click “Activate”

When all is said and done, you’ll have:

• A field which stores the date a Task was closed
• A field which calculates the difference between the date a Task was created and the date it was closed
• A Workflow Rule that will evaluate any time a Task’s Status is changed to “Completed” and
• A Workflow Action that updates your Close Date field with today’s date.

Go ahead and give it a try!

Feel free to submit your questions or comments below.

Good luck!

Thanks,

Ben