I send the data from a custom object up to a supplier (also on Salesforce) where they work it and move it from a status of New through to Approved. One of the interim statuses (not mandatory) is "Return to Vendor", ie where its bounced back to us for a query/error.
What I want to achieve is a timer that can track the age in business hours of the object from CreatedDate through to when the status is marked Approved.
And here's the complicated bit - I want the timer to stop when the object is marked as "Returned to Vendor", and then to start again when we send it back for continued processing.
I'm using the following field to get the gross age in business hours:
IF(DATEVALUE(CreatedDate)=TODAY(), ROUND((NOW() - CreatedDate)*24,0) ,ROUND(8*( (5*FLOOR((TODAY()-DATE(1996,01,01))/7) + MIN(5, MOD(TODAY()-DATE(1996,01,01), 7) + MIN(1, 24/8*(MOD(NOW()-DATETIMEVALUE('1996-01-01 13:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) + MIN(5, MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) + MIN(1, 24/8*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 13:00:00'), 1))) )) ), 0))
Is it possible to wrap that in another formula that will start/stop the count when the Status="Returned to Vendor"? Note an object might go to status Returned to Vendor more than once before it is finally marked as either Approved or Rejected.
Or is this a job for Apex?