After battling with formulas to calculate the difference in 'business hours' between 2 date/time fields, it became apparent that there needs to be something simple for this, after getting it 90% there it seems that there are flaws with formulas where it gives a negative value if the dates fall on the same day, but one is outside business hours.
This occurred using the below formula -
ROUND( 12 * (
( 5 * FLOOR( ( DATEVALUE( Replied_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( Replied_Date_Time__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 12 * ( MOD( Replied_Date_Time__c - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( Received_Date_Time__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( Received_Date_Time__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 12 * ( MOD( Received_Date_Time__c - DATETIMEVALUE( '1900-01-08 08:00:00' ), 1) ) )
)
)
),
2 )
+
ROUND( 9 * (
( FLOOR( ( DATEVALUE( Replied_Date_Time__c ) - DATE( 1900, 1, 6) ) / 7) +
MIN(1,
MOD( DATEVALUE( Replied_Date_Time__c ) - DATE( 1900, 1, 6), 7) +
MIN( 1, 24 / 9 * ( MOD( Replied_Date_Time__c - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1 ) ) )
)
)
-
( FLOOR( ( DATEVALUE( Received_Date_Time__c ) - DATE( 1900, 1, 6) ) / 7) +
MIN( 1,
MOD( DATEVALUE( Received_Date_Time__c ) - DATE( 1900, 1, 6), 7 ) +
MIN( 1, 24 / 9 * ( MOD( Received_Date_Time__c - DATETIMEVALUE( '1900-01-06 09:00:00' ), 1) ) )
)
)
),
2)
You can enter Business hours & holidays within the set up and yet these cannot be referenced in formulas.
It would be a really good thing if you could simply use a basic 'BUSINESS HOURS' command within a formula. Especially for a business such as ours where we have different hours at weekends and don't open bank holidays.
