1

I am not sure how to fix this problem? Any help will be appreciated. It gave me Error: Incorrect number of parameters in function 'IF()' expected 3, returned 2

IF( Cannot_Respond__c = True, 0, 
IF( ISBLANK( Request_Due_Date__c ) && ISBLANK( Request_Response_Date__c ) , 
    ROUND(ROUND( 24 * ( 
    ( 5 * FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7) + 
    MIN(5, 
    MOD( TODAY() - DATE( 1900, 1, 8), 7) + 
    MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( TODAY() ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
    ) 
    ) 
    - 
    ( 5 * FLOOR( ( Request_Received_Date__c - DATE( 1900, 1, 8) ) / 7) + 
    MIN( 5, 
    MOD( Request_Received_Date__c - DATE( 1996, 1, 1), 7 ) + 
    MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE(Request_Received_Date__c) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
    ) 
    ) 
    ), 
    0 ) /24, 0)), 


    IF( ISBLANK(Request_Response_Date__c ) ,
        IF(Today()< Request_Due_Date__c, 0, 
        ROUND( ROUND( 24 * ( 
        ( 5 * FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7) + 
        MIN(5, 
        MOD( TODAY() - DATE( 1900, 1, 8), 7) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( TODAY() ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
        ) 
        ) 
        - 
        ( 5 * FLOOR( ( Request_Due_Date__c - DATE( 1900, 1, 8) ) / 7) + 
        MIN( 5, 
        MOD( Request_Due_Date__c - DATE( 1996, 1, 1), 7 ) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE(Request_Due_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
        ) 
        ) 
        ), 
        0 ) /24, 0) )), 

    /*End of Second Part*/ 

        IF(ISBLANK( Request_Due_Date__c ), 
            IF(Request_Received_Date__c = Request_Response_Date__c, 1, 

            ROUND( ROUND( 24 * ( 
            ( 5 * FLOOR( ( Request_Response_Date__c - DATE( 1900, 1, 8) ) / 7) + 
            MIN(5, 
            MOD( Request_Response_Date__c - DATE( 1900, 1, 8), 7) + 
            MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Response_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
            ) 
            ) 
            - 
            ( 5 * FLOOR( ( Request_Received_Date__c - DATE( 1900, 1, 8) ) / 7) + 
            MIN( 5, 
            MOD( Request_Received_Date__c - DATE( 1996, 1, 1), 7 ) + 
            MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Received_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
            ) 
            ) 
            ), 
            0 ) /24, 0))) , 

        /*End of Third Part*/ 

            IF(OR(NOT(ISBLANK(Request_Due_Date__c )) , NOT(ISBLANK(Request_Response_Date__c))),
                IF(Request_Due_Date__c = Request_Response_Date__c, 1, 
                 IF(Request_Due_Date__c < Request_Response_Date__c, 0,
                ROUND( ROUND( 24 * ( 
                ( 5 * FLOOR( ( Request_Response_Date__c - DATE( 1900, 1, 8) ) / 7) + 
                MIN(5, 
                MOD( Request_Response_Date__c - DATE( 1900, 1, 8), 7) + 
                MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Response_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
                ) 
                ) 
                - 
                ( 5 * FLOOR( ( Request_Due_Date__c - DATE( 1900, 1, 8) ) / 7) + 
                MIN( 5, 
                MOD( Request_Due_Date__c - DATE( 1996, 1, 1), 7 ) + 
                MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Due_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
                ) 
                ) 
                ), 
                0 ) /24, 0))) , 0) ) 
        /*End of 4th Part*/

I went back I was able to get first two IF statement work, but Last one with ** is not being executed at all where the due date is blank. No errors but the last if is not executing.

 IF(OR(NOT(ISBLANK(Request_Due_Date__c )) , NOT(ISBLANK(Request_Response_Date__c))),
    IF(Request_Due_Date__c = Request_Response_Date__c, 1, 
        IF(Request_Due_Date__c > Request_Response_Date__c, 0,
        ROUND( ROUND( 24 * ( 
        ( 5 * FLOOR( ( Request_Response_Date__c - DATE( 1900, 1, 8) ) / 7) + 
        MIN(5, 
        MOD( Request_Response_Date__c - DATE( 1900, 1, 8), 7) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Response_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
        ) 
        ) 
        - 
        ( 5 * FLOOR( ( Request_Due_Date__c - DATE( 1900, 1, 8) ) / 7) + 
        MIN( 5, 
        MOD( Request_Due_Date__c - DATE( 1996, 1, 1), 7 ) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Due_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
        ) 
        ) 
        ), 
        0 ) /24, 0))),


IF( ISBLANK(Request_Response_Date__c ) , 
    IF(Today()< Request_Due_Date__c, 0, 
        ROUND( ROUND( 24 * ( ( 5 * FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7) + 
        MIN(5, MOD( TODAY() - DATE( 1900, 1, 8), 7) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( TODAY() ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
        )  ) 
        - 
        ( 5 * FLOOR( ( Request_Due_Date__c - DATE( 1900, 1, 8) ) / 7) + 
        MIN( 5, 
        MOD( Request_Due_Date__c - DATE( 1996, 1, 1), 7 ) + 
        MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE(Request_Due_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
        ) 
        ) 
        ), 
        0 ) /24, 0)), 


**IF(ISBLANK( Request_Due_Date__c ), 
    IF(Request_Received_Date__c = Request_Response_Date__c, 1, 

    ROUND( ROUND( 24 * ( 
    ( 5 * FLOOR( ( Request_Response_Date__c - DATE( 1900, 1, 8) ) / 7) + 
    MIN(5, 
    MOD( Request_Response_Date__c - DATE( 1900, 1, 8), 7) + 
    MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Response_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1 ) ) ) 
    ) 
    ) 
    - 
    ( 5 * FLOOR( ( Request_Received_Date__c - DATE( 1900, 1, 8) ) / 7) + 
    MIN( 5, 
    MOD( Request_Received_Date__c - DATE( 1996, 1, 1), 7 ) + 
    MIN( 1, 24 / 24 * ( MOD( DATETIMEVALUE( Request_Received_Date__c ) - DATETIMEVALUE( '1900-01-08 17:00:00' ), 1) ) ) 
    ) 
    ) 
    ), 
    0 ) /24, 0)),**

0)))
1
  • 2
    I would truly consider other alternatives to make this more maintainable. Looks like you are going to waste a lot of time in the future should anything break or need to be modified. Not to mention what if you leave and someone else needs to modify this, they will surely have to take a lot of time to understand what this formula does. Maybe instead of a long formula field you could use Apex to achieve what you are trying to accomplish. Another idea, since you seemed to have copied/pasted most of your calculations, maybe you could split this into 2 formula fields and make it much more readable.
    – Z33dawg
    Apr 5 '19 at 20:49
3

The first thing I do Is transfer the formula to notepad++ where there is some support for automatically identifying opening/closing parentheses:

sample

Notice here the red parentheses. I then used that to strip out massive parts of your formula to limit it to the parts that we want to look at (the if statements). Then I made sure that each parameter of the if functions are on their own line to separate them out and count how many there are.

Missing params

The red indicates some of the places that I found that are missing parameters (there are several).

Tip

When you are writing a formula, what I always do, before adding in any real content, is make sure to add all the parentheses and commas exist before I start typeing.

So an IF() starts off like so:

IF(
 , /* <-- Parameter 1 */
 , /* <-- Parameter 2 */
   /* <-- Parameter 3 */
)

this helps me make sure that I never forget a parameter. Then I can add in the extra stuff:

IF(
    ISBLANK(Request_Response_Date__c ), /* <-- Parameter 1 */
    IF(
        Today()<Request_Due_Date__c, 
        0, 
        ROUND()
    ), /* <-- Parameter 2 */
    /* <-- Parameter 3 */
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.