1

I want to report on time spend on each stage on a case (created to its closed). I believe i have to first create a formula field on case object which can show time stamp (days,hours and mins) , it also need to stop calculating time once the case is closed and have to stick with business hours (Monday - Friday, 8AM - 6PM) Any help would be really appropriated. Many thanks.

2

This is a question that gets asked unfortunately often. The easy answer is to install this free app.

Otherwise you'll have to create a trigger that insert's a record, usually an activity, every time the status of the case changes. Your trigger should populate an "end" field on the last activity that was inserted. The challenge here is defining business hours. You'll have to perform some calculations to figure those out (the app does this for you automatically); this is where that formula field you mentioned would come in handy.

Code Sample

This should get you close:

public void createStatusChangeEvents(Map<Id, Case> updatedCases, Map<Id, Case> oldCases) {
    List<Event> newEvents = new List<Event>();
    List<Event> lastEvents = new List<Event>();

    // Get the most recent activity per case
    updatedCases = new Map<Id, Case>([
        SELECT Id, (
            SELECT Id
            FROM Events
            WHERE Type = 'Status Change'
            ORDER BY CreatedDate DESC
            LIMIT 1
        )
        FROM Case
        WHERE Id IN :updatedCases
    ]);

    for (Case c : updatesCases.values()) {
        // If the status changes
        if (c.Status != oldCases.get(c.Id).Status) {
            // Create a new event
            newEvents.add(new Event(
                StartTime = system.now(),
                WhatId = c.Id,
                CustomStatusTrackingField__c = c.Status
            ));

            // If the case already has an event
            if (c.Events != null && !c.Events.isEmpty()) {
                // Update the existing event
                c.Events[0].EndTime = system.now()
                lastEvents.add(c.Events[0]);
            }
        }
    }

    insert newEvents;
    update oldEvents;
}

Example Formula from here

Weekday Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7), 

  0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 

Weekend Days Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*2)

The issue with these formula sis that they assume you're open 24/5. If you want to record only part of the day the calculations get much more complicated (9am-5pm for example). Again, the app should handle this part for you.

Simpler formula without business hours:

IF(ISBLANK(EndDate), TODAY()-StartDate, EndDate - StartDate)

Notes:

  • The field names I used may not be correct
  • I used event because it already has start/end date fields but you can use task if you want to add custom fields or a custom object altogether, if your org has them a big object would be great for this purpose

Disclaimer

Making this a re-usable answer so I don't have to keep writing them.

  • I have a similar requirement down the road, but for Opportunity stages instead of case stages. Can this product be used for Opportunities? – TemporaryFix Aug 30 '18 at 15:37
  • I don't think the app supports opps but you could definitely use the code sample I have and adapt it to opps. – gNerb Aug 30 '18 at 15:38
  • Thanks gNerb. How will this work if a case stage regresses? Do the days get added to the previous stage, or do they stay retained to the original stage? – TemporaryFix Aug 30 '18 at 15:52
  • A new record is inserted with the stage that they changed it too and the new record would track the time in that stage. This would give you a "real" history of what happened (you'd be able to tell that the stage regressed). – gNerb Aug 30 '18 at 15:54
  • Hi gNerb, thanks for your response , I will first try with the app ,If i get what I'm looking for then easy way out for me :) . only thing I can't figure out is the way it is calculating. I opened and closed the case in 12 mins duration and the value in the field "Case Age In Business Hours" is "0.207". Not sure if there is way to make it show normal time which will be easy to understand. Also is it only showing this random number after the case is closed and the field stays blank while the case is still open. – Inniboy Sep 5 '18 at 8:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.