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?

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) {
SELECT Id
WHERE IsDefault = true
LIMIT 1
];

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

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

}

return median;
}

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