We accomplish this with multiple calculated fields. SHORT ANSWER You'll need 1. A calculated field for each status change (timestamp). 2. A calculated field for the difference between the two timestamps. LONG ANSWER PART ONE To create a calculated field for a status change: Create a calculated field ( FIELDNAME ) and then write a formula that says, "If the status equals this status (use the 3 letter code - ex. Complete = "CPL"), then if the field's currently blank, put the time of right now. Otherwise, leave it blank). Format:
IF(Status=' AAA ',IF(ISBLANK( FIELDNAME ),$$NOW, FIELDNAME ), FIELDNAME )
Ex. The timestamp for when a request went On Hold is a field called "On Hold Time":
IF(Status=' ONH ',IF(ISBLANK( On Hold Time ),$$NOW, On Hold Time ), On Hold Time )
Make 2 timestamps for this (or use an existing one, like Entry Date, Actual Completion Date, etc) Pro-Tips:
Make sure your two timestamps use the same format (Date/Time or Date)
This only works the first time the field goes into a particular status. It won't overwrite if you take it out of the status and put it back in.
PART TWO Then use a formula to calculate the difference between the two timestamps. Workfront allows the options for
DATEDIFF: Calendar days between 2 events
WEEKDAYDIFF: Business days between 2 events
WORKMINUTESDIFF: Minutes?
Format:
WEEKDAYDIFF( Date 1 , Date 2 )
For the difference between Entry Date and On Hold Time, the formula would look like this.
WEEKDAYDIFF( Entry Date , On Hold Time )
Vincent Goodwin The Capital Group Companies
