Lets says we have a date, call it Just_a_date__c
And we have number of business days, call it Number_of_business_days_to_add__c

Business day in this context means Monday to Friday
(gosh thank god for a 5 days working week for me and you:)

Now to crack this using only formulas, we need to understand a few things (limitations and concepts)

1. Formula fields do not support ‘for loops’! So we cant iterate over each next-day, evaluate the day of week and add if appropriate. Go to https://salesforcemann.wordpress.com/2016/11/15/add-days-to-a-date-excluding-weekends-i-e-saturday-and-sunday/ to know what I mean
2. There is no standard formula to know a day of the week

Concept:
3. When we add lets say 10 business days to a date, we visualize that these 10 are the 5 working days of two weeks. We will try to establish a pattern first e.g. if the start date is Sunday we will crossover 1 weekend. However, if the start date is Monday we will crossover 2 weekend. Same for Wednesday and until Friday.

4. Lets focus on Monday first,
=
4 business days = 0 weekend
5 business days = 1 weekend
9 business days = 1 weekend
10 business days = 2 weekends
11 business days = 2 weekends
15 business days = 3 weekends
So the formula would be: Just_a_date__c + Number_of_business_days_to_add__c + 2 * (FLOOR(Number_of_business_days_to_add__c/5))

5. Lets focus on Tuesday now,
3 business days = 0 weekend
4 business days = 1 weekend
8 business days = 1 weekend
9 business days = 2 weekends
10 business days = 2 weekends
14 business days = 3 weekends
So the formula would be: Just_a_date__c + Number_of_business_days_to_add__c + 2 * (FLOOR((Number_of_business_days_to_add__c+1)/5))

6. Similarly, for Friday the formula would be
Just_a_date__c + Number_of_business_days_to_add__c + 2 * (FLOOR((Number_of_business_days_to_add__c+4)/5))

7. How about if the start date is a Saturday or a Sunday?
For Sunday
1 business day = 0 weekends
6 business days = 1 weekend
10 business days = 1 weekend
11 business days = 2 weekends
So the formula would be
Just_a_date__c + Number_of_business_days_to_add__c + 2 * (FLOOR((Number_of_business_days_to_add__c-1)/5))

8. For Saturday
1 business day = 0 weekends
6 business days = 1 weekend
10 business days = 1 weekend
11 business days = 2 weekends

This looks identical to Sunday, but there is one BIG difference. We need to add 1 extra day to start dates that are Saturday because Everyday Saturday has 0.5 weekend ahead of it i.e 1 day which is the Sunday!
So the formula would be
Just_a_date__c + 1 + Number_of_business_days_to_add__c + 2 * (FLOOR((Number_of_business_days_to_add__c-1)/5))

9. Wait, we forgot to figure out how to tell which day of the week is the start date! Here goes…. You know what day was 01-01-1900… It is an open secret ….. a Monday! So cool that using this secret information we can now tell which day was, is or will be any date!

CASE(
MOD(Start_Date__c - DATE(1900, 1, 1), 7),
0, "Monday",
1, "Tuesday",
... ...
)

10. And finally combining all the gyan into one formula:

//start date
Just_a_date__c +
//add 1 days if Saturday
case(
  MOD(
    Just_a_date__c - DATE(1900, 1, 1), 7),5,1,0
)
+
//add number of business days
Number_of_business_days_to_add__c
+
//add number of non working days = 2 * number of weekends
2 * (
        FLOOR(
          (Number_of_business_days_to_add__c +
            case(MOD(Just_a_date__c - DATE(1900, 1, 1), 7),
                    0,0, //Monday
                    1,1,
                    2,2,
                    3,3,
                    4,4,
                    5,-1, //treat Saturday as Sunday
                    6,-1,
                    0
                    )
          )/5
        )
      )

And that’s all folks!
Thank you.

If you want to see another approach for calculating this, visit:
https://salesforcemann.wordpress.com/2016/12/11/the-ultimate-salesforce-formula-to-add-business-days-to-a-date-part-2/

Advertisements