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.

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 | |
  • 2
    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 Jun 15 '15 at 20:41
  • @ToddSprinkel even better! – powlo Jun 16 '15 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 Feb 21 '16 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 | |

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.