1

I am trying to create a new field formula that will check if one value is not blank and complete the formula, but if the field is blank then it runs the other IF section of the formula. Below is the formula I have built so far, but its giving me an error saying the second IF needs to be removed:

IF( 
AND(NOT(ISNULL( Assigned_On__c)), NOT(ISNULL(Due_Date_to_Partner__c))),  Assigned_On__c -     Due_Date_to_Partner__c, 0
)
IF 
AND(NOT(ISNULL( Assigned_On__c)), NOT(ISNULL(Due_Date_to_Customer__c))),  Assigned_On__c - Due_Date_to_Customer__c,0
)
3

Since both your IF clauses are checking that Assigned_On__c isn't empty, you can simplify this to something like:

IF( 
  ISBLANK( Assigned_On__c ),
  0,
  Assigned_On__c - IF( ISBLANK( Due_Date_To_Partner__c ), Due_Date_To_Customer__c, Due_Date_To_Partner__c )
)

Which basically says, if Assigned_On__c is empty, return 0; else, return Assigned_On__c minus whichever of the other dates isn't empty. (I assume you have a validation rule or something that requires at least one of them to be filled in.)

Note the use of ISBLANK() and not ISNULL() -- ISNULL() is deprecated and should not be used when creating new formulas.

| improve this answer | |
  • I tested your formula with both blank values and even when both dates are null it leaves the formula null which was I think the poster was initially worried about. Your version wins at 246 characters :) – Jenny B Nov 21 '14 at 11:56
  • I updated my answer with another way of writing the formula based on your suggestion which is smaller yet. – Jenny B Nov 21 '14 at 15:51
  • "I assume you have a validation rule or something that requires at least one of them to be filled in" would prevent any empty values. – shannonsans Nov 23 '14 at 21:50
2

What you are try to do is called a nested if, basically you want to put your second if in the else condition of your first if like this:

IF( 
    AND(NOT(ISBLANK( Assigned_On__c)), NOT(ISBLANK(Due_Date_to_Partner__c))),  Assigned_On__c -     Due_Date_to_Partner__c, 
    IF 
        AND(NOT(ISBLANK( Assigned_On__c)), NOT(ISBLANK(Due_Date_to_Customer__c))),  Assigned_On__c - Due_Date_to_Customer__c,0
    )
)

Another way to write it could be:

IF(Assigned_On__c > Due_Date_to_Partner__c, Assigned_On__c - Due_Date_to_Partner__c,
  IF(Assigned_On__c > Due_Date_to_Customer__c, Assigned_On__c - Due_Date_to_Customer__c, 0))

Smaller version(82 characters) based on Shannons answer:

BLANKVALUE(Assigned_On__c - BLANKVALUE(Due_Date_to_Partner__c,  Due_Date_to_Customer__c), 0)

This will give you 0 vs null when any of the dates are null.

Edit: Switched all formulas to BLANK vs NULL based on Shannon's note and docs:

Use BLANKVALUE instead of NULLVALUE in new formulas. BLANKVALUE has the same functionality as NULLVALUE, but also supports text fields. Salesforce will continue to support NULLVALUE, so you do not need to change existing formulas.

Use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL, but also supports text fields. Salesforce will continue to support ISNULL, so you do not need to change any existing formulas.

| improve this answer | |
  • Although this is woefully more expensive than it needs to be. – sfdcfox Nov 21 '14 at 0:26
  • @sfdcfox - edited with an alternative formula, is that what you had in mind? – Jenny B Nov 21 '14 at 1:00
  • NULLVALUE() is deprecated, and should not be used when writing formulas. Use BLANKVALUE() instead. (Otherwise, nice work on getting the compile size down to 81 characters!) – shannonsans Nov 23 '14 at 21:51

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.