0

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))))))))
  • 1
    How Proposal_Date__c and = Proposal_Due_Date__c are related? – o-lexi Nov 9 '17 at 18:06
0

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.

| improve this answer | |

Your Answer

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