0

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?

5

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)));
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.