Hi SF Community,
I know there have been many posts about calculating a value (formula field) when subtracting to date fields.
However, I'm looking for something similar to this (https://success.salesforce.com/answers?id=9063A000000sskhQAA#!/feedtype=SINGLE_QUESTION_SEARCH_RESULT&id=90630000000gkp8AAA), but I need to add criteria to calculate based on a 9am-5pm, Mon-Fri range.
Total criteria:
- derive a value that returns a sum number of hours (with 1 decimal place). I.e. 9.5hrs.
- calculate the difference between 2 date/time fields.
- take into consideration Mon-Fri (business week)
- take into consideration 9am-5pm (business hours)
Thanks for the help!
Nick
6 respuestas
Did you try using this one?
DOCUMENTATION
Sample Date Formulas
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5
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.
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.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 )
