0

I have a scenario to calculate the Case Response Time in Formula field so that it will change the remaining time dynamically.

My Scenarios is,

I have Priorities P1, P2, P3, P4

Timeframe to respond the P1 case is 30 min
Timeframe to respond the P2 case is 2 hour
Timeframe to respond the P3 case is 8 hour
Timeframe to respond the P4 case is 24 hour

So, when P1 case is created, at that time we need to catch the datetime it is created and show the response time.

For ex: Case Created On 19/12/2016 3:20 PM

        Response Time - 30 min  (because timeframe for P1 is 30 min)

After 10 min, if there is no response on the case then we need to show, Response Time - 20 min

After 30 min, if there is no response on the case then we need to show, Response Time - 0 min

After 40 min, if there is no response on the case then we need to show, Response Time - -10 min

similarly for all the priorities.

Now when the case owner changes, response time should stop wherever it is.

So, below is the formula I've wrote to get the response time. I did it almost

IF( caseOwnerChanged__c , 

(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60)) 
&' hr'& 
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60)),2 ) 
&' min'), 


(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60)) 
&' hr '& 
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) ,60)),2 ) 
&' min') 
)

caseOwnerChanged__c - Checkbox field, which returns true if owner changed

ResponseTime__c - formula field which returns 30 for P1, 120 for P2, 480 for P3...

caseOwnerChangedOn__c - Returns datetime when owner changed

Now, problem is I want to get only mins if no hours are involved. For ex,

If P1,

Response Time is coming as 0 hr 30 min

I wanted to get like,

Response Time - 30 min

lly for P3 andP4, If response time is going in negative, am getting like,

Response Time - -4 hr -20 min

I want to get like,

Response Time - -4 hr 20 min

Help me to overcome these problems. Thanks

  • Have you considered using Entitlements instead? They literally do what you're trying to do, and a lot more. – sfdcfox Jan 5 '17 at 5:07
  • My client is not aware of entitlement and also they need train their staffs to use entitlement. So I need to do it custom manner – mohammed azarudeen Jan 5 '17 at 10:58
0

You are almost there!

You need nested IF conditions here.

Check the value of

FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60

before preparing the actual value, if its 00, go for min only, else include hr as well. Something like this:

IF( caseOwnerChanged__c , 
IF(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60) = 0,
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60)),2 ) 
&' min',
(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60)) 
&' hr'& 
LEFT( TEXT(ABS(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60))),2 ) 
&' min')
), 

IF(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60) = 0,
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) ,60)),2 ) 
&' min',
(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60)) 
&' hr '& 
LEFT( TEXT(ABS(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) ,60))),2 ) 
&' min') 
)
)

I have added ABS functions on the min section where you are having hr value as well, ensuring you only get single negative sign.

  • Thanks for your time in my question. Whenever I do nested if condition there, it is saying "Compiled formula is too big to execute (9,069 characters). Maximum size is 5,000 characters (Related field: Formula)". – mohammed azarudeen Dec 19 '16 at 12:15
  • Okay, to avoid that, create another formula field which will store only (((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) this portion. Then in current formula, use the other formula field and calculate the hr & min. – Santanu Halder Dec 20 '16 at 6:27
  • already tried with another fomula like you said but it is again saying the same character limit exception – mohammed azarudeen Dec 20 '16 at 10:06
  • I just tried the same in my developer org and its working absolutely perfect. Please make sure you are not adding any other conditions in the formula field. Which type of org are you using? – Santanu Halder Dec 20 '16 at 10:20
  • No am not adding anything extra. Am using sandbox(unlimited edition) – mohammed azarudeen Dec 20 '16 at 10:40

Your Answer

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

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