Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Tommy Sunderland 16

# Calculate first response time on Opportunity (not Case) in minutes

Hello, I have an involved request that I'm hoping someone can help me solve.
• On Opportunities we track first response time, calculated by taking Date/Time that the first activity is logged (after the opportunity is created) and subtracting from it the Date/Time that the Opportunity was created. The difference between the two calculates a first response time in minutes.
• The team has asked me to take into account our business hours (9AM - 5PM) in this equation, meaning that if an opportunity is created after 5PM, then the first response time should only be calculated after 9AM the following morning. So an opportunity created at 5:00 PM on a Thursday and responded to at 9:15 AM Friday morning would show a first response time of 15 minutes (and not 960 minutes due to the off hours).
• Note: our current Default business hours are set to "24 Hours" and not to 9 to 5. We do not have separate business hours set in Salesforce for each office, just FYI
• Important note: We have multiple offices, so the proposed solution needs to take this into account.
Is this possible? Would that be via formulas or other declarative solutions? I'm not an experienced developer.

• Someone pointed me to this solution, but this appears to account for hours, not minutes. I'm looking for minutes
• Someone else recommended customizing this package. The package is meant for Cases (I'm dealing with Opportunities). Additionally, I don't have great coding skils and I worry that the appropriate solution using this package may be beyond my skillset. However, I'm open to an advice.
Thank you!
Alain Cabon
Hi Tommy,

When you want to take into account your business hours (9AM - 5PM), it is always a tricky problem because the "real" business hours take also into accounts the hollydays and the timezones.

The most accurate and "easy" solution is to use a trigger here.

One line of code is sufficient in Apex and all the details of business hours are take into account directly.

```trigger EvtCalculateBusinessHours on Event (after insert, after update, after delete) {
Set<Id> oppIds = new Set<Id>();

Map<Id,DateTime> mOppMinEvtCreateddate = new Map<Id,DateTime>();
Map<Id,DateTime> mOppMinStartdatetime = new Map<Id,DateTime>();

List<Event> evts = null;

if (Trigger.isInsert || Trigger.isUpdate) {
evts = Trigger.new;
} else {
if (Trigger.isDelete) {
evts = Trigger.old;
}
}

if(evts != null) {
for (Event evt:evts) {
if (evt.WhatId != null) {
String sobjectType = evt.WhatId.getSObjectType().getDescribe().getName();
if (sobjectType == 'Opportunity') {
// default null date in case of deleting the last event
mOppMinEvtCreateddate.put(evt.WhatId,null);
mOppMinStartdatetime.put(evt.WhatId,null);
}
}
}
}

if (oppIds.size() >0) {
// Get the min event dates for all the opportunities
AggregateResult[] groupedResults = [select WhatId, min(createddate) minCreateDate, min(startdatetime) minStartdatetime from Event where whatId in :oppIds group by whatId] ;
for (AggregateResult ar : groupedResults)  {
System.debug('What ID:' + ar.get('WhatId'));
System.debug('Min CreatedDate:' + ar.get('minCreateDate'));
mOppMinEvtCreateddate.put((Id)ar.get('WhatId'), (DateTime)ar.get('minCreateDate'));
mOppMinStartdatetime.put((Id)ar.get('WhatId'), (DateTime)ar.get('minStartdatetime'));
}
// Get all the created date for all the opportunities sent to the trigger
List<Opportunity> opps = [select id,createddate from opportunity where id in :oppIds];

List<Opportunity> oppsUpdated = new  List<Opportunity>();
for (Opportunity opp:opps) {
// The diff method comes back in milliseconds, so we divide by 3600000 to get hours.
// diff(businessHoursId, startDate, endDate): returns the difference in milliseconds between a start
//   and end Datetime based on a specific set of business hours
DateTime OppMinEvtCreateddate = (DateTime)mOppMinEvtCreateddate.get(opp.Id);
DateTime OppMinEvtStartdatetime = (DateTime)mOppMinStartdatetime.get(opp.Id);
if (OppMinEvtCreateddate == null) {
} else {
system.debug('opp.CreatedDate :' + opp.CreatedDate);
system.debug('OppMinEvtCreateddate :' + OppMinEvtCreateddate);
system.debug('OppMinEvtStartdatetime :' + OppMinEvtStartdatetime);
System.debug('First_Response_Time:' + First_Response_Time);
}
}
if (oppsUpdated.size() > 0) {
update oppsUpdated;
}
}
}```

The remaining problems:
1. You need two triggers: one for the events and one for the tasks (the same as above replacing just Event with Task)
2. Only one definition of business hours has been used above (the default one) and that could be not sufficient.
3. Each record of Case has its own BusinessHoursId but not the Event object. BusinessHours is closely linked with the cases.
4. You must initate all the pre-existing opportunities for the First_Response_Time__c field (empty by default)
The initialization of the First_Response_Time__c field for all the opportunities is not difficult if you have the "dataloader" tool.

How many events are there in your org?

Alain Cabon
In minutes: 60,000.0 instead of 3,600,000.

1 minute = 60 seconds = 60 x 1000 milliseconds = 60,000 ms.

1 hour = 60 minutes = 60 x  60,000 ms.= 3,600,000 ms