Salesforce Formula For Calculating Case Age In Hours Excluding Weekends

This is a modification of the Salesforce formula for finding the number of business hours between two dates. Here is the Salesforce formula:

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 )

We need to make a few modifications in order to calculate case or lead age for Monday-Friday (no business hours). The Salesforce formula above calculates for business hours between 9am-5pm. We need the same formula for 24 hour days. First, as posted in the Salesforce article we can simply change all the eights to whatever number we want to calculate so change the first 8 to 24 and we can get rid of 24 / 8 since 24 / 24 = 1 it is not needed. The next modification is we need to choose a reference date/time so for a 24 hour period we change ‘1900-01-08 16:00:00’ to ‘1900-01-08 00:00:00’. The end result is:

ROUND(24*( 
(5*FLOOR((TODAY()-DATE(1996,01,01))/7) + 
MIN(5, 
MOD(TODAY()-DATE(1996,01,01), 7) + 
MIN(1, (MOD(NOW()-DATETIMEVALUE('1996-01-01 00:00:00'), 1))) 
)) 
- 
(5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) + 
MIN(5, 
MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) + 
MIN(1, (MOD(CreatedDate-DATETIMEVALUE('1996-01-01 00:00:00'), 1))) 
)) 
), 0)

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Do the Math      
 

*