0

The use case: I am trying to calculate a date that is 3 "Bank Business days" after the application create date. Which includes Saturday and not Sunday.

The Below Formula is taking into consideration both Saturday and Sunday.

How would I adjust this formula to account for Saturday and not Sunday.

CASE(
MOD(Application_Created_Date__c - DATE(1900, 1,7),7),

0, (Application_Created_Date__c) + 3 + FLOOR((3-1)/5)*2,

1, (Application_Created_Date__c) + 3 + FLOOR((3)/5)*2,

2, (Application_Created_Date__c) + 3 + FLOOR((3+1)/5)*2,

3, (Application_Created_Date__c) + 3 + FLOOR((3+2)/5)*2,

4, (Application_Created_Date__c) + 3 + FLOOR((3+3)/5)*2,

5, (Application_Created_Date__c) + 3 + CEILING((3)/5)*2,

6, (Application_Created_Date__c) - IF(3>0,1,0) + 3 + CEILING((3)/5)*2,
null)

Thank you,

Improve this question
0

1 Answer 1

Reset to default
0

Add 4 to any day where the next 3 days includes a Sunday. (Case 0 is Sunday. Case 6 is Saturday.)

CASE( MOD(Application_Created_Date__c - DATE(1900, 1,7),7),
0, (Application_Created_Date__c) + 3 + FLOOR((3-1)/5)*2,
1, (Application_Created_Date__c) + 3 + FLOOR((3)/5)*2,
2, (Application_Created_Date__c) + 3 + FLOOR((3+1)/5)*2,
3, (Application_Created_Date__c) + 3 + FLOOR((3+2)/5)*2,
4, (Application_Created_Date__c) + 4 + FLOOR((3+3)/5)*2,
5, (Application_Created_Date__c) + 4 + CEILING((3)/5)*2,
6, (Application_Created_Date__c) - IF(3>0,1,0) + 4 + CEILING((3)/5)*2, null)
Improve this answer
3
  • I have entered this formula and it is still counting Sunday. It's exactly how you have it.
    – user60972
    Commented Oct 10, 2018 at 13:52
  • CASE( MOD(Application_Created_Date__c - DATE(1900, 1,7),7), 0, (Application_Created_Date__c) + 3 + FLOOR((3-1)/5)*2, 1, (Application_Created_Date__c) + 3 + FLOOR((3)/5)*2, 2, (Application_Created_Date__c) + 3 + FLOOR((3+1)/5)*2, 3, (Application_Created_Date__c) + 1 + FLOOR((3+2)/5)*2, 4, (Application_Created_Date__c) + 2 + FLOOR((3+3)/5)*2, 5, (Application_Created_Date__c) + 2 + CEILING((3)/5)*2, 6, (Application_Created_Date__c) - IF(4>0,1,0) + 3 + CEILING((3)/5)*2, null)
    – user60972
    Commented Oct 10, 2018 at 20:03
  • Is it working for you now?
    – Dean M.
    Commented Oct 12, 2018 at 1:20

You must log in to answer this question.