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

• Chatter
Feed
• 0
• 0
• 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)
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)
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