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) +
    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) +
    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?


  • Not really. What you are wanting to do will require iteration of the case history object and would require code. I have implemented something like this before with Business hours and KPI's taking into account start / stop Case Status', but it only updated the field on status change and not real time like a formula field would. I do not think you can reliably (and with good maintainability) do this via a formula field or workflow rules / field updates – Eric Aug 18 '15 at 20:59
  • Thanks Eric, I have history turned on and what I'm looking at is essentially pulling the list of records first (id with CreatedDate) to get their start time. Then I take all the history records for the Status field into a mysql db, and run an analysys in PHP for each record. Did you do it in Apex? – user1517428 Aug 18 '15 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.