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.
1 Answer
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? Aug 30, 2018 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.– gNerbAug 30, 2018 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? Aug 30, 2018 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).– gNerbAug 30, 2018 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. Sep 5, 2018 at 8:58