Ask Search:
Lisa RoisumLisa Roisum 

Date Formulas that exclude weekends?

Does anyone know how to create an auto-calculated date field that excludes weekend days and only includes working days?  The formula we are trying to get to is:

We have a currently set up date field that is filled in by the requestor.  So:

Date Field Manually Entered + 5 working days = New, calculated date field


Any help would be greatly appreciated!!!
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Here are some expamples you can use:

Knowledge Article Number: 102787


Description

Formula - Weekend Days or Workdays Calculation




Resolution

Purpose:
Calculate the number of days between two dates while excluding weekends or excluding weekdays.

Formulas provided:

  1. Weekday Count Formula
  2. Weekend Days Count Formula

Steps to create:

  1. Create a formula field that returns a number.
  2. Paste in one of the two formulas.
  3. Replace StartDate__c and EndDate__c with your custom field values.
  4. If using Date/Time fields
    • Replace with DATEVALUE(YourCustomDateTime__c) instead.

Assumptions/Limitations/Background:

  • Weekdays are defined as Mon-Fri and weekends as Sat & Sun
  • Holidays are not addressed
  • June 6, 1985 is a long distant Monday used as a reference point
  • The result will include BOTH the START and END dates.
    • Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
    • Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
    • Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
  • If you use another Formula field as the start or end dates you MAY hit a compilation limit.
    • Workaround - Use workflow rules to save the output of the formula fields into a regular date field.
  • Be sure to TEST the formulas FIRST.

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)