• Craig POLLOK 2
  • NEWBIE
  • 0 Points
  • Member since 2022

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 2
    Replies
I am attempting to create a workflow rule field update formula that will return/update a date.  This formula will take a given date (DATE 1) plus (+) a processing days number (NUMBER 1) and return/update a date (DATE 2) during the work week.


Field Definitions
DATE 1 = Given Date
NUMBER 1 = Processing Days
DATE 2 = Result
 
Basic Formula:
DATE 1 + NUMBER 1 = DATE 2

Example 1
If I set a date field to 11/4/2014 (Tuesday) and want to add 3 business days, it should return 11/7/14 (Friday).
​DATE 1: 11/4/14 (Tuesday)
NUMBER 1: 3 (Business Days)
DATE 2: 11/7/14 (Friday)
 
Example 2
If I set a date field to 11/4/2014 (Tuesday) and want to add 10 business days, it should return 11/18/14 (Tuesday).
​DATE 1: 11/4/14 (Tuesday)
NUMBER 1: 10 (Business Days)
DATE 2: 11/18/14 (Tuesday)
 
Please note, DATE 2 should always be during the week.  Any help would be greatly appreciated. Thank you!
 
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
  • 4