Skip to main content
Topics#Data ManagementPregunta de Karthikeyan Thangavel
Karthikeyan Thangavel (Salesforce) preguntó en #Data Management
15 mar 2017, 15:25

How to Case Age in Business Hrs (Formula Field)?

Custom Field Name : Business_Hrs__c (Formula-Returns Number)

 

Custom Field Name : First_Touch_Date__c (Date/Time Field)

 

Things to know about First Touch Date:

  • This field always gets populated once a case status changes from New Status to a different status
  • All Business Hrs calculation are based on this field insted of Case Date/Time Opened Standard Field

 

Things to know about Business Hrs

  • Business Day begins and 8AM
  • Business Day ends at 5PM
  • Business Day = 9 Business Hours
  • Business Week = 45 Business Hours (if no holidays)
  • Saturday and Sunday are not included
  • Holidays are not included
  • If a case comes in after business hours, the Business Hrs calculation would not begin until the following Business Day
  • Likewise if a case is TOUCHED after hours, and it makes Business Hrs set as 0 (Example, a case gets created around 5:05 PM, and a user changes the status from New to In Progress around 5:45 PM, then the business hrs should be 0 and start calculating the next business day from 8AM. Around 10 AM, the expected output should be 2 Hrs)
  • TOUCHED means a case changes from New to different Case Status which is First_Touch_Date__c

How do I calculate this in a formula ?

 

How to incorporate business holidays in the formula as well? 

 

 
24 respuestas
  1. Steve Molis (You Owe Me a Beer, Inc.)
    15 mar 2017, 15:43
    Here you go

     

    DOCUMENTATION

     

    Sample Date Formulas | Salesforce

     

    https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0&language=en_US&release=206.12

     

     

     

    Finding the Number of Business Hours Between Two Date/Times

     

    The formula for finding business hours between two Date/Time values expands on the formula for finding elapsed business days. It works on the same principle of using a reference Date/Time, in this case 1/8/1900 at 16:00 GMT (9 a.m. PDT), and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. work day.

    ROUND( 8 * (
       ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
        MIN(5, 
         MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
         MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
        ) 
       )
     -
       ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
         MIN( 5,
          MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
          MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
        )
       ) 
      ), 
    0 )

     

    You can change the eights in the formula to account for a longer or shorter work day. If you live in a different time zone or your work day doesn’t start at 9:00 a.m., change the reference time to the start of your work day in GMT. See A Note About Date/Time and Time Zones for more information.
Escribir una respuesta...
0/9000