Expand my Community achievements bar.

Join us for our Coffee Break Sweepstakes on July 16th! Come ask your questions or share your use cases on Creative Briefs for a chance to win a piece of Workfront swag!

Cycle Time Calculation - NEED HELP

Avatar

Level 5
Hi everyone, I am in great need of your help. I am trying to calculate how many days an entry spent in a particular status. For example i have an entry with status value of 1 on 10/1. By 10/15 the status value changed to 2. When this change occurs, i want to be able to calculate that the entry spent 14 regular days or however many business days in status value of 1. Can anyone help? has anyone done something similar to this? I have tried to capture in a custom form the date the status value changed, however found out thru workfront help calculated fields do not update other fields, which is a bummer. Any other ideas? Thank in advance. Nuria Munguia
4 Replies

Avatar

Level 2
This would be very useful. Hopefully someone has a calculation;/solution for us. :) Jeffrey Banks

Avatar

Level 6
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

Avatar

Level 2
This will only calculate going forward, right? So if I want/need it for historical, I'm out of luck. Heather Theriault MetLife

Avatar

Level 5
Vincent Thank you so much! Actually this is what I was able to figure out thru trial and error last week. I knew i was close with my formula, the piece i was missing was setting the field name for when the IF statement was false. Thank you again! Yes, this only calculates going forward, and like Vincent mentioned it does not overwrite if the status gets set back again.