function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SkeeterSkeeter 

Formula to calculate duration in specific status

IF(
ISPICKVAL(Status,'Not Started'),
NOW() -CreatedDate,null)
I'm trying to create a formula on the task that will calculate how long a task has been in the status of not started and keep that information when the status changes.  

Currently, my formula populates the field but nulls it out when the status is change to anything else.
Andy BoettcherAndy Boettcher
You'll need to do a Workflow Action to fill a regular number field with that same information - the formula field is always recalculated at runtime so you'll lose your value when the status flips.
Xavier MuñozXavier Muñoz
Hi, following the Andy's previous answer:

Note: This solution has two assumptions:
  1. Initial status of the task is 'Not Started'  (otherwise, you could not use the createdDate in your formula)
  2. Once the status changes from 'Not Started', it is not possible to change it to 'Not Started' again (your formula counts from CreatedDate so it would count time of other statuses).

SOLUTION
  • Create a number field (e.g. aux__c) with predefined value = 0.
  • Workflow rule on task with following criteria (triggered each time the record is updated). 
    AND(ISPICKVAL(PRIORVALUE(Status),'Not Started') , NOT(ISPICKVAL(Status,'Not Started')))
  • Field update of the workflows, update aux__c field:  
    Now()-CreatedDate
  • Update your formula:  
    IF(
ISPICKVAL(Status,'Not Started'),
NOW() -CreatedDate,aux__c)
When the task is created with Not Started status, your formula will show now()-createdDate. Then, when the status changes, the workflow will update the aux__c with the duration of the status, and the formula will show its value :D