Difference between two dates and add business days?

Question

I have two dates,

Created date and date processed.

I want to find the midway between these two days but make sure the midway date lands on a business day.

  DATEVALUE(createdDate)  + (( Date_processed__c  - DATEVALUE(CreatedDate)) / 2)

This returns a date value between these two dates but does not consider if it lands on a business day. How can I do that or is it not possible in a formula field?

Answer 1

If your business days are more complex than just a weekend/weekday split, you'll need to do this in a trigger or a batch job. Formulas do not have access to the "Business Days" as configured in salesforce. Theres a decade old idea for support, so I wouldn't expect it anytime soon.

These are the formulas salesforce provides as examples for determining Weekend/Weekday values, excluding one or the other.

Weekday Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7), 
 0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 

Weekend Days Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*2)

---

Heres a sample of that same formula, written in apex. You'll need either a trigger or a batch job to run it.

public static DateTime BusinessDayMidpoint(DateTime startDate, DateTime endDate) {
    BusinessHours hours = [
        SELECT Id
        FROM BusinessHours 
        WHERE IsDefault = true
        LIMIT 1
    ];

    Long diff = BusinessHours.diff(hours.Id, startDate.Date(), endDate.Date());

    DateTime median = DateTime.newInstance(startDate.getTime() + (diff / 2));

    while (!BusinessHours.isWithin(hours.Id, median)) {
        median = median.addDays(1); 
    }

    return median; 
}

System.debug(BusinessDayMidpoint(DateTime.now(), DateTime.now().addMonths(1)));

Answer 2

Try the following formula:

DATEVALUE(createdDate) + ( Date_processed__c  - DATEVALUE(CreatedDate)) / 2 +
CASE(
    MOD( ROUND(( Date_processed__c  - DATEVALUE(CreatedDate)) / 2, 0) - DATE(1900, 1, 6), 7),
    0, 2,
    1, 1,
0)