Melinda Layten has documented an automated timestamp field on status change in this thread: View this on Workfront >
Note that you need to add the custom calculated field without a formula, apply (or save) so the field name is available and then add the formula that self-references. That will capture the date/time of one status change (using on-hold instead of anything but in progress as the current formula does). So we can tweak this approach to capture the first time a project is put on hold AND the first time it is taken off hold (Remember to add the fields without formula and save the form before going back to add the formula)
Name
Formula
First OnHold
IF(Status='ONH',IF(ISBLANK(First OnHold),$$NOW,First OnHold),First OnHold)
First OffHold
IF(ISBLANK(First OffHold),IF(Status!='ONH',IF(ISBLANK(First OnHold),"",IF(ISBLANK(First OffHold),$$NOW,First OffHold),First OffHold)),First OffHold)
FirstHoldDiff
IF(ISBLANK(First OnHold),0,IF(ISBLANK(First OffHold),DATEDIFF($$NOW,First OnHold),DATEDIFF(First OffHold,First OnHold)))
Name
Formula
First OnHold
IF(Status='ONH',IF(ISBLANK(First OnHold),$$NOW,First OnHold),First OnHold)
First OffHold
IF(ISBLANK(First OffHold),IF(Status!='ONH',IF(ISBLANK(First OnHold),"",IF(ISBLANK(First OffHold),$$NOW,First OffHold),First OffHold)),First OffHold)
FirstHoldDiff
IF(ISBLANK(First OnHold),0,IF(ISBLANK(First OffHold),DATEDIFF($$NOW,First OnHold),DATEDIFF(First OffHold,First OnHold)))
Which is fine for the first time a project is put on hold. What about after that? We just continue the logic, but referencing if the first Offhold is blank or not - if it's blank, don't do anything. If it's not blank, it's the second fields turn to shine:
Name
Formula
Second OnHold
IF(ISBLANK(First OffHold),"",IF(Status='ONH',IF(ISBLANK(Second OnHold),$$NOW,Second OnHold),Second OnHold))
Second OffHold
IF(ISBLANK(First OffHold),Second OffHold,IF(ISBLANK(Second OffHold),IF(Status!='ONH',IF(ISBLANK(Second OnHold),"",IF(ISBLANK(Second OffHold),$$NOW,Second OffHold),Second OffHold)),Second OffHold))
SecondHoldDiff
IF(ISBLANK(Second OnHold),0,IF(ISBLANK(Second OffHold),DATEDIFF($$NOW,Second OnHold),DATEDIFF(Second OffHold,First OnHold)))
We can continue this as many times as you want to track - Post the formula into Word or Notepad, replace 'Second' with 'Third' and 'First' with 'Second' (need to do it in descending order, or you'll have too many named 'Second'). Once you've added as many as you want, you can sum all the diffs at the end to get total time on hold:
Total OnHold Days
SUM(FirstHoldDiff,SecondHoldDiff)
While the post looks long, the form itself isn't too complex: And by the way, if you decide on six (for example), if you check to see when the Sixth On Hold is populated, you can add seven, eight and nine to the form, update the total formula and keep going! Barry Buchanan Work Management Australia