2 Replies Latest reply on May 24, 2019 8:14 AM by Dan Bontrager

# Showing business hours between Actual Start & End fields in Central Time Zone

I have hit a roadblock and I'm wondering if someone on the Communities 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 link and started with that formula:

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, 6 standard hours or 5 daylight savings hours behind GMT (we’ll just use 6 in the formula).  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 6: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 6:00:00'), 1)))

))

),2)

In some cases, this returns the correct results...changes with no time difference (i.e. start and end both set to the same date and time, so it shows 0.00) or when the start and end are on the weekend (so it always shows 0.00).  But when the times are on a weekday, whether within or outside of 8 AM - 5 PM CT, the calculation is not correct.  Below are screen shots showing the Implementation Business Hours field using this formula and it is next to an Implementation Total Hours field (which is just Actual End Date - Actual Start Date).  I also show the Actual Start and End fields for reference.

Can anyone help me modify the above formula to get the results documented in the below test cases?

(1A)

Implementation Business Hours should be 0.00 hours (4:45 AM - 7:15 AM)

(2A)

Implementation Business Hours should be 1.00 hour (8 AM - 9 AM)

(3A)

Implementation Business Hours should be 2.25 hours (8 AM - 10:15 AM)

(4A)

Implementation Business Hours should be 4.50 hours (11 AM - 3:30 PM)

(5A)

Implementation Business Hours should be 0.75 hours (4:15 PM - 5:00 PM)

* Now changed Start date to 4/18 to encompass a 2-day change window during workweek

(1B)

Implementation Business Hours should be 9.00 hours (8 AM – 5 PM on 4/18)

(2B)

Implementation Business Hours should be 10.00 hours (8 AM – 5 PM on 4/18 & 8 AM – 9 AM on 4/19)

(3B)

Implementation Business Hours should be 11.25 hours (8 AM – 5 PM on 4/18 & 8 AM – 10:15 AM on 4/19)

(4B)

Implementation Business Hours should be 13.50 hours (11 AM – 5 PM on 4/18 & 8 AM – 3:30 PM on 4/19)

(5B)

Implementation Business Hours should be 9.75 hours (4:15 PM – 5 PM on 4/18 & 8 AM – 5 PM on 4/19)

• ###### 1. Re: Showing business hours between Actual Start & End fields in Central Time Zone

Update:  Discovered I was entering the GMT time incorrectly.  "13:00:00" would represent 8 AM Central Standard Time, so I switched the formula to use that and now all but one test case passes.  This still fails:

(2A)

Implementation Business Hours should be 1.00 hour (8 AM - 9 AM)

- 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

(2B)

Implementation Business Hours should be 10.00 hours (8 AM – 5 PM on 4/18 & 8 AM – 9 AM on 4/19)

- Same issue as (2A)

Can anyone help with the formula to get this test case to pass?  Thanks!

• ###### 2. Re: Showing business hours between Actual Start & End fields in Central Time Zone

Update: I got the formula to work for Central Time.  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).

I am going to mark this discussion as answered and start another one for more specific questions with this formula:

1 of 1 people found this helpful