Salesforce formula: Business hours formula not working for start time outside business hours and end time within business hours

Question

I have hit a roadblock and I'm wondering if someone on here can help me out. I am looking to have a formula that shows the business hours between the Actual End Date (BMCServiceDesk__Actual_End_Date__c) and Actual Start Date (BMCServiceDesk__Actual_Start_Date__c). I found the below links to help with creating the formula:

https://success.salesforce.com/answers?id=9063A000000iXTlQAM

Salesforce formula: Calculate business hours between two dates

I have the formula modified now like the below code. We have a 9-hour work day (8 AM - 5 PM) and we are in the Central Time Zone, so we use ’13:00:00’ in the formula (13:00:00 GMT = 1 PM GMT = 8 AM CT). We also want to round the ‘hours’ values to 2 decimal places.

ROUND(9*(
(5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)- 
DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(BMCServiceDesk__Actual_Start_Date__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_Start_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(BMCServiceDesk__Actual_End_Date__c)-DATE(1996,01,01), 7) +
    MIN(1, 24/9*(MOD(BMCServiceDesk__Actual_End_Date__c-DATETIMEVALUE('1996-01-01 13:00:00'), 1)))
))
),2)

In most of my test cases this is working as expected. When both the start and end are outside the business hours or on the weekends, the value is returned as ‘0.00’. When the start is within the business hours and, regardless if the end is within the business hours or after it, the value is correctly calculated.

The issue comes when the start is outside the business hours and the end is within business hours. For example, a change with a start of ‘4/19/19 4:45 AM’ and an end of ‘4/19/19 9:00 AM’ should calculate to ‘1.00’ (8 AM to 9 AM); instead, it calculates as ‘-8.00’. It appears that any time prior to 8:00 AM shows as ‘-8.00’ back to 1:00 AM – 12:59 AM corrects to ‘1.00’ and is correct even back into previous day business hours.

In another example, if a cross an entire day with a change with a start of ‘4/18/19 4:45 AM’ and an end of ‘4/19/19 9:00 AM’, it should calculate to ‘10.00’ (one entire 9-hour day and 8 AM – 9 AM on the second day); instead, it calculates as ‘1.00’.

Can anyone help modify the above formula to get correct business hour calculations for start times outside business hours and end times within business hours?

Suraj Suraj · Accepted Answer · 2019-05-25T16:43:28

Try this

FLOOR((BMCServiceDesk__Actual_End_Date__c - BMCServiceDesk__Actual_Start_Date__c)/7)*5*9 + 
MIN(5,FLOOR(MOD((BMCServiceDesk__Actual_End_Date__c - BMCServiceDesk__Actual_Start_Date__c),7)))*9+

IF((TIMEVALUE(BMCServiceDesk__Actual_End_Date__c)-TIMEVALUE("12:00:00.00"))/(3600*1000)>16,0,MIN(9,(TIMEVALUE(BMCServiceDesk__Actual_End_Date__c)-TIMEVALUE("12:00:00.00"))/(3600*1000))) - IF((TIMEVALUE(BMCServiceDesk__Actual_Start_Date__c)-TIMEVALUE("12:00:00.00"))/(3600*1000)>16,0,MIN(9,(TIMEVALUE(BMCServiceDesk__Actual_Start_Date__c)-TIMEVALUE("12:00:00.00"))/(3600*1000)))

Salesforce formula: Business hours formula not working for start time outside business hours and end time within business hours

1 Answers