[SalesForce] IF statement with AND statement: If date field is NULL and today’s date is 5 days before due date

I'm not getting any errors but I'm not getting the results that I'm looking for. The field is empty if the proposal date is empty and if today's date is 5 days before the proposal due date is. The following formula (the first part of the formula) is supposed to populate the formula field with a text if the proposal due date is in 5 days from today.

I tried to use "AND" in case the Proposal date field is not populated and if today's date is 5 days before the due date.

IF( AND(Proposal_Date__c = NULL,Proposal_Due_Date__c = TODAY()+5), "Proposal is due SOON", 
IF( Proposal_Date__c = Proposal_Due_Date__c - 5, "Proposal Due in 5 Days", 
IF( Proposal_Date__c = Proposal_Due_Date__c - 4, "Proposal Due in 4 Days", 
IF( Proposal_Date__c = Proposal_Due_Date__c - 3, "Proposal Due in 3 Days", 
IF( Proposal_Date__c = Proposal_Due_Date__c - 2, "Proposal Due in 2 Days", 
IF( Proposal_Date__c = Proposal_Due_Date__c - 1, "Proposal Due in 1 Day", 
IF( Proposal_Date__c = Proposal_Due_Date__c, "Proposal Due Today", 
IF( Proposal_Date__c >= Proposal_Due_Date__c, "Proposal is Past Due",NULL))))))))

Best Answer

Try something closer to this:

IF(
 AND(
  ISBLANK(Proposal_Date__c),
  Proposal_Due_Date__c = Today() + 5
 ),
 "Propsal is due SOON",
 IF(
  Proposal_Due_Date__c - Proposal_Date__c = 0,
  "Propsal is due Today",
  IF(
   Proposal_Due_Date__c - Proposal_Date__c < 0,
   "Propsal is PAST DUE",
   IF(
    Proposal_Due_Date__c - Proposal_Date__c > 5,
    "<default text or blank>",
    "Proposal Due in " & Proposal_Due_Date__c - Proposal_Date__c & " days"
   ) 
  )
 )
)

At the very least, this will reduce the number of if statements in your formula.

Be Advised:

This might help answer your question as it was asked, but I fear that your original formula may not solve your business use case.

My guess is the proposal date represents when the proposal was submitted, performed or otherwise completed. If this is true, it drastically changes the formula. If it's not true, than I question the use case.

Related Topic