we have a workflow rule for business hours which will populate whenever status field changes. and rule is for only 8 hours. and normal salesforce instance is 24hours. Yesterday evening it gave correct hours, but today morning when i have created new one, business hours field has changed to 0. and after 12:00 it is working correctly. please explain the functionality how it is working for 8 hours
status field has 3 picklist values, "Work in Progress analysis","Work in Progess Development",Work in Progress QA". whenever, status has been changed from one value to other, then time will be changing. that difference time between 2 status values is calculated in business hours field.
Formula used is as below:
8*(
(5*FLOOR((TODAY()-DATE(1996,01,01))/7) +
MIN(5,
MOD(TODAY()-DATE(1996,01,01), 7) +
MIN(1, 24/8*(MOD(NOW()-DATETIMEVALUE('1996-01-01 07:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) +
MIN(1, 24/8*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 07:00:00'), 1)))
))
)
1 risposta
Hello Madhuri,
I would like to help. The following article covers the sample formulas and one at the very end of this article applies to your situation:
https://help.salesforce.com/apex/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US (https://help.salesforce.com/apex/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US)
I have also included the details for you here:
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 )
Please let us know if you need further assistance, otherwise please take a moment to mark a post as "Best Answer" to help others in the community with similar questions. Thank you all for your contribution to Success Community.
