# Calculate Working days excluding Holidays in formula field

this code is not calculate the holidays

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

Your formula looks like its taken from Formula - Weekend Days or Workdays Calculation and does what it is designed to do which is to only count weekend days and ignore weekday days. As you can see, there are no references in it to anything but your input dates and simple constants; there are no references to any objects that represent holidays.

The BusinessHours Class does take account of holidays AFAIK, though from a quick Google I haven't found any clear-cut examples of its use for your purpose and writing test cases looks like a challenge - see Creating Holidays and Business Hours in Test Code.

• The BusinessHours class works extremely well excluding holidays and only counting hours during business hours. I have implemented it for a KPI (SLA) use case. Test methods were not bad at all since the business hours are available during tests.
– Eric
Jan 25 '15 at 4:33

If you want something that compiles a smidge smaller (in one test: 1,319 chars instead of 2,081 chars):

``````(FLOOR((TODAY() - DATEVALUE(Target_Date__c)) / 7) * 5)                      /* compute the number of complete workweeks         */
+ WEEKDAY(TODAY())                                                          /* add enddate's weekday                            */
- WEEKDAY(DATEVALUE(Target_Date__c))                                        /* subtract startdate's weekday                     */
+ MIN(MAX(WEEKDAY(DATEVALUE(Target_Date__c)) - WEEKDAY(TODAY()), 0), 1) * 5 /* add 5 if startdate is later in week than enddate */
+ FLOOR(WEEKDAY(DATEVALUE(Target_Date__c)) / 7)                             /* add 1 if startdate is a Saturday                 */
- FLOOR(WEEKDAY(TODAY()) / 7)                                               /* subtract 1 if enddate is a Saturday              */
``````

. . . this leverages the new WEEKDAY() function, made available with one of the '18 releases.

The BEST answer is provided on Salesforce's Sample Date Formulas Help page:

``````(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )
``````

It's elegant and compiles smaller.