0

I have a requirement where in, i have to exclude public holidays listed below: (14-Jan-2015, 26-Jan-2015, 06-Mar-2015, 17-Sep-2015, 02-Oct-2015, 22-Oct-2015, 11-Nov-2015, 12-Nov-2015, 13-Nov-2015, 25-Dec-2015) plus weekends to calculate number of days between start date and end date.

I have created formula field which excludes weekends but i need help to exclude holidays mention above using below formula field.

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

Appreciate suggestions.

Improve this question

2 Answers 2

Reset to default
1

For a more robust solution, you should use the built in <Holiday> object.

This lets the Sys Admin set up any holidays, so you don't need to push updates for the new year.

It looks like the Holiday object isn't accessable for formula fields or validation rules, so you'll have to write it in a trigger.

If you want to calculate working days between two dates then you would have to iterate over every day in the range, and then iterate over every holiday rule and see if it applies.

To reduce the burden you might want to split out the non recurring ones, since if you know its not in a date range then you don't need to recheck.

Improve this answer
3
  • 3
    It's not actually necessary to iterate over the days, as there is a built-in method to calculate the difference. BusinessHours.diff() You will, however, have to convert this to something that makes sense to a person, as this method returns a Long that is the difference in milliseconds. I've written something in our org that calculates a duration in BusinessHours. A batch class runs hourly to update this value. Documentation here: developer.salesforce.com/docs/atlas.en-us.apexcode.meta/…
    – Todd Sprinkel
    Commented Jun 15, 2015 at 20:41
  • @ToddSprinkel even better!
    – powlo
    Commented Jun 16, 2015 at 11:44
  • @ToddSprinkel Depending on the structure of the working hours, some may actually prefer to iterate through and determine a more exact count of "business days." It is easier if you can just assume 8 hours per day, at which point diff makes a lot of sense. But the method can be hard to implement depending on the exact nature of your requirements.
    – Adrian Larson ♦
    Commented Feb 21, 2016 at 23:20
0

Can you create another formula to test whether a holiday date is between the End Date and Start Date? i.e. IF(AND((End date < Jan 1 < Start Date),(Jan 1 != Sat or Sun)), NumberofHolidayDays__c = 1, 0). Then subtract NumberofHolidayDays__c from your formula?

By the way, here's a better formula for calculating weekend days (I've seen a lot of errors with that formula you are using)

    (FLOOR(( DateValue(Date_2__c)- DateValue(Date_1__c))/7)*2) 
+ (IF((MOD( DateValue(Date_1__c)- DATE(2000,1,2),7) = 6 && MOD( DateValue(Date_2__c) - DATE(2000,1,2),7) != 6) 
|| (MOD( DateValue(Date_1__c)- DATE(2000,1,2),7) != 6 && MOD( DateValue(Date_2__c) - DATE(2000,1,2),7) = 6), 1, 
IF(MOD(DateValue(Date_1__c)- DATE(2000,1,2),7) > MOD( DateValue(Date_2__c) - DATE(2000,1,2),7), 2, 0)))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .