Our business wants to calculate the number of hours that have lapsed between two date/time fields, but kicker is we want to only calculate the hours that happen between Monday-Friday 24/5. Here is our current formula, but we're getting incorrect values. Any assistance to show us the error of our ways would be appreciated.
IF(AND(
datevalue( datetime1 ) <> datevalue(datetime2), Record_Type_Name__c = 'TL') ,
(((5 *(FLOOR((datevalue(datetime1)-DATE(1900, 1, 8)) / 7 )) + MIN(5, MOD(datevalue(datetime1) - DATE( 1900, 1, 8), 7 )))
-
(5 *( FLOOR((datevalue(datetime2)-DATE(1900, 1, 8) ) / 7 )) + MIN(5, MOD( datevalue(datetime2)- DATE( 1900, 1, 8), 7 )))) * 24)
+ROUND(MOD(((DATETIMEVALUE(TEXT(datevalue(datetime2 )+ 1) &' 05:00:00')) - datetime2)* 24,24),0)
+ value(mid(text(datetime1 ),12,2)) - 5
,(datetime1 - datetime2)*24)
You mean like this?
https://help.salesforce.com/apex/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US
Finding the Number of Business Hours Between Two Date/TimesThe 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.
