2

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)))
Improve this question

2 Answers 2

Reset to default
1

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)
Improve this answer
2
  • You must have been writing your reply at the same time I was! LOL!
    – crmprogdev
    Commented Apr 15, 2013 at 3:55
  • @jkraybill Nice answer and use of comments to clarify things. I was convinced there was a WORKDAYS function, then remembered I'd seen this in Google Spreadsheets recently, lol!
    – Andrew Fawcett
    Commented Apr 15, 2013 at 8:04
0

I suspect you've not received a reply because you've not explained what you're trying to do with your code as in what's it's use and what's your logic behind it?

That having been said, I think this post from the SF Discussion Boards provides the following code as a verified solution to your question (just change the fields):

  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))))

From the above, it appears to me that you perhaps "over thought" your code a bit and that's why its been returning only 1's and 6's (I never could understand the purpose of the 999). I also noticed that your code uses today() in some places and now() in other places. I'd recommend you stick with today() throughout your code since its days that are important to you, not minutes or seconds (a TimeDate value when what you want is the number of days between today() and the CreatedDate).

Improve this answer
2
  • 1
    Per the thread that we both linked to, there is an issue with this particular formula in that it apparently returns "5" when start and end date are the same. But that thread contains everything you should need to get a working formula.
    – jkraybill
    Commented Apr 15, 2013 at 4:19
  • @jkraybill, Good catch! I quickly scanned the 2 page thread and didn't study the details. I primarily noted that the original post was checked as the solution and figured the poster could read the follow-up posts for the details related to how the original post got checked as the solution. I figured it had "likely" been edited to incorporate any revisions discussed later.
    – crmprogdev
    Commented Apr 15, 2013 at 13:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .