[SalesForce] Formula for finding business days since last interaction

Trying to figure out why this formula is only returning a 1 or a 6. I am trying to figure out how many business days from when a case was created or from the last update. 

IF( ISBLANK(Last_Case_Comment_Update__c), (CASE(MOD( today() - DATE(1985,6,24),7),
0 , CASE( MOD( now()- CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( now()- CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( now()- CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( now()- CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( now()- CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( now()- CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( now()- CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( now()- CreatedDate )/7)*5)),

(CASE(MOD( today() - DATE(1985,6,24),7),
0 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( now()- Last_Case_Comment_Update__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( now()- Last_Case_Comment_Update__c )/7)*5)))

Best Answer

These formulas do my head in! So no, can't tell you why that particular formula is failing. However, here is an alternative formula that, per this community thread, seems to work as intended. (I've subbed out Start Date with your Last Case Comment Update, and End Date with now()).

IF(Last_Case_Comment_Update__c <> now(),
    IF( /** Condition */
        AND(
            (5 - (CASE(MOD( Last_Case_Comment_Update__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD(  now()  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
            ((( now()  -   Last_Case_Comment_Update__c ) + 1) < 7)
        ),
        /** Value to YES */
        (
            (CASE(MOD(  now()  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD(  Last_Case_Comment_Update__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))
        ),
        /** Value to NOT */
        (   
            ((FLOOR((( now()  -  Last_Case_Comment_Update__c ) - (CASE(MOD(  Last_Case_Comment_Update__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(  Last_Case_Comment_Update__c  - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
            (CASE(MOD(  now()  - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))
        )
    )
,0)
Related Topic