• Vorix
  • NEWBIE
  • 0 Points
  • Member since 2008

  • Chatter
    Feed
  • 0
    Best Answers
  • 9
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies
The following formula will calculate the number of working days (inclusive) between 2 dates. A working day is defined as Monday to Friday. Even if the start or end dates are a weekend, these are accommodated.

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  -   Start_Date__c ) + 1) < 7)),
((CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  -  Start_Date__c ) - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))


The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.

I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.
  • January 05, 2009
  • Like
  • 9
The following formula will calculate the number of working days (inclusive) between 2 dates. A working day is defined as Monday to Friday. Even if the start or end dates are a weekend, these are accommodated.

IF(AND((5 - (CASE(MOD( Start_Date__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  -   Start_Date__c ) + 1) < 7)),
((CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  -  Start_Date__c ) - (CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD(  Start_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD(  End_Date__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))


The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.

I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.
  • January 05, 2009
  • Like
  • 9