I am sure a lot of you guys have faced this requirement as a Salesforce Administrator. The management wants to know how long does it take for a case to be closed by agent, so you provide the simplest down-to-earth answer. 

IF( !ISBLANK(Date_Time_Closed__c), 
    Date_Time_Closed__c - CreatedDate, 
    IF( !ISBLANK(ClosedDate), 
         ClosedDate - CreatedDate, 
         NOW() - CreatedDate 
    ) 
)

Notice that I have both Date_Time_Closed__c (value set after case is initially closed) and CloseDate in here. Reason being, the case can be reopened after closing. In our case scenario, cases are rarely reopened and we are more concerned of the initial closing, hence the formula is set up this way. Basically, this formula will generate a number which you can think of it as the days difference between these two date/time field.

Simple and straightforward, right?

Not so fast. The agents are not really happy, because it "seems" like they have taken longer time to solve cases, considering the fact that they are not working 7 days a week. If an agent works on the case on Friday, the agent can only carry on next Monday since they are off during weekends.

What do we do?

Well, after spending some time googling Internet, here's what I found to be most appropriate to our case here.

Source URL: http://1771.co/track-business-days-with-a-formula-field-in-salesforce/

Honest to god, I don't know who came out with this formula, but this person is definitely a genius! Since the used case is similar to what we need, this formula can be used right away.

IF( IsClosed, 
  CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
    0 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
    1 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
    2 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
    3 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
    4 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
    5 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
    6 , CASE( MOD(DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
    999 
  ) + (FLOOR((DATEVALUE(Date_Time_Closed__c) - DATEVALUE(CreatedDate))/7)*5-1) 
  , 
  CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 
    0 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
    1 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
    2 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
    3 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
    4 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
    5 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
    6 , CASE( MOD(TODAY() - DATEVALUE(CreatedDate),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
    999 
  ) + (FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*5-1) 
)

Don't ask me why it the date is 1985-6-24 or minus 1, if you have the same exact requirement where you need to calculate the business days of a case, this formula will definitely suit your needs. The downside of this formula is that it is actually taking around 3900 characters (5000 characters is the limit) to compile. If you are going to use this formula in another formula, you will probably exceed the Salesforce limit. What you probably can do to slightly reduce the compiled size is to create custom fields to hold open date and close date value in DATE format, that way you don't have to type cast DATETIME fields like CreatedDate. You can also convert the whole logic into DATETIME format by replacing TODAY() to NOW(), and DATE(1985,6,24) to DATETIMEVALUE('1985-06-24 00:00:00'). By doing so, you can save roughly around 1000 characters.

Bonus

Have you ever wondered how to display a health-bar like to reflect the case age? It is very simple, and here's how!

IF( 
    Days_to_Close__c < 3, 
    IMAGE("/resource/slds_colors/green.png","Green",10,(Days_to_Close__c / 10 * 200)), 
    IF( 
        Days_to_Close__c < 5, 
        IMAGE("/resource/slds_colors/orange.png","Orange",10,(Days_to_Close__c / 10 *200)), 
        IF( 
            Days_to_Close__c < 10, 
            IMAGE("/resource/slds_colors/red.png","Red",10,(Days_to_Close__c / 10 * 200)), 
            IMAGE("/resource/slds_colors/red.png","Red",10, 200) 
        ) 
    ) 
) & 

IF(Days_to_Close__c < 10, 
    IMAGE("/resource/slds_colors/grey.png","",10,( (10 - Days_to_Close__c) / 10 *200)), 
    ""
) & 

IF( ISPICKVAL(Status, "Closed") , ' (Closed)', '' )

Basically, I am using 3 colors to denote each stage. If the Days_to_Close__c is less than 3 days, it will display green block image of height 10px and width of (Days_to_Close__c / 10 and * 200)px.

case-green-health

Likewise, if Days_to_Close__c is more than 3 days but less than 5 days, it will display orange block of height 10px and width of Days_to_Close__c / 10 * 200)px.

case-orange-health

Lastly, if the Days_to_Close__c is more than 5 days but less than 10 days, it will display red block of height 10px and width of (Days_to_Close__c / 10 * 200)px.

case-red-health

What happens if Days_to_Close__c is longer than 10 days? We sure won't want to see the bar breaks the screen, do we? In this case, we simply set it to display red block of height 10px and width of 200px. Nothing further.

case-red-health-max

The very last step is to fill up the remaining part with grey blocks. We check how many grey blocks we need by subtracting Days_to_Close__c from number 10. I am sure up to this point, you guys are curious why it is number 10, not some other numbers. Well, it doesn't matter what number you choose in the formula anyway, as long as you know how to calculate the remaining blocks. I am using 10 as base number because it is easier to do the calculation considering I am using 10 days for the range of Days_to_Close__c. If your case deals with 5 days top, then probably using 5 days will be more appropriate. Again, you can use different base height and width however you want to suit your business needs. This is just an example showing you how the health-bar is done in Salesforce!

Well, that's it! I hope you guys enjoy it! See you next time~

Post was published on , last updated on .

Like the content? Support the author by paypal.me!