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

    Dan Bontrager
      Share This:

      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:

      Salesforce formula: Calculate business hours between two dates - Stack Overflow

       

      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)