cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ronald_01 Regular Visitor
Regular Visitor

Combine multiple if statements and be able to add priority

Hi All,

 

I'am creating a new column in which I want to be able to determine if a project is active.

A project is active when:

Startdate is <= today and when the enddate is >= today. But sometimes the enddate is not filled.

If that is the case, the formula must look at a second value (End_Date_PO_Sub_agreement), when the second value is not filled the formula has to check a third value (Expected_Project_End_Date). 

 

In some cases all the values are filled and then the priority must be the enddate.

 

Active projects = IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[cxsrec__End_date__c] >= TODAY(); "active" ; 
    IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[Expected_Project_End_Date_TMC__c] >= TODAY(); "active" ; 
        IF(Placement[cxsrec__Start_date__c] <= TODAY() && Placement[End_Date_PO_Sub_agreement__c] >= TODAY(); "active" ; BLANK())))

Any ideas would be much appericated.  Thanks for any and all help in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Combine multiple if statements and be able to add priority

Sure, just flip the logic a little:

 

 

// Create a variable "end_date" that takes on the correct 
// value depending upon what columns are blank

Active projects pseudo = 

VAR end_date = 

   IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test

      IF(ISBLANK(Placement[End_Date_PO_Sub_agreement__c]); //second if test (true condition from first if)
         Placement[Expected_Project_End_Date_TMC__c]; //if PO End date is blank, use Project end date
         Placement[End_Date_Sub_agreement__c] //Otherwise, use the PO End Date
      ); //end second if test

      Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank
   ); //end first if test

// Return is based upon the value of this variable "end_date"
RETURN 
  IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(); 
     "active"; //true condition
     BLANK() //false condition
  )

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


8 REPLIES 8
Super User
Super User

Re: Combine multiple if statements and be able to add priority

Psuedo code but the general idea should work:

 

Active projects = VAR end_date = IF(ISBLANK([cxsrec_End_date__c]),IF(ISBLANK([Expected_Project_End_Date_TMC__c]),[End_Date_PO_Sub_agreement__c,[Expected_Project_End_Date_TMC__c]),[cxsrec_End_date__c)

RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(), "active", BLANK())

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Ronald_01 Regular Visitor
Regular Visitor

Re: Combine multiple if statements and be able to add priority

Hi @Greg_Deckler,

 

Thank you very much for your quick response. Unfortunately the provide solution issn't working.

I tried to adapt the formula slightly but I receive a Syntax error for 'RETURN'.

 

Active projects pseudo = VAR cxsrec__End_date__c = IF(ISBLANK(Placement[cxsrec__End_date__c]);IF(ISBLANK(Placement[Expected_Project_End_Date_TMC__c]);(Placement[End_Date_PO_Sub_agreement__c]);(Placement[Expected_Project_End_Date_TMC__c]);Placement[cxsrec__End_date__c]);

RETURN IF(Placement[cxsrec__Start_date__c] < TODAY() && Placement[cxsrec__End_date__c] >= TODAY(); "active"; BLANK()))

Greetings Ronald

Super User
Super User

Re: Combine multiple if statements and be able to add priority

You have a strange ";" at the end of the first line. I don't think that needs to be there.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Ronald_01 Regular Visitor
Regular Visitor

Re: Combine multiple if statements and be able to add priority

Hi @Greg_Deckler,

 

I removed the strange ; at the end of the first line. But now I'am receive another error stating, 'Too many arguments were passed to the IF function. The maximum argument count for the function is 3.' Do you have another solution to solve this issue? Maybe the SWITCH function? But I don't no how to use is. 

 

Greetings Ronald

Super User
Super User

Re: Combine multiple if statements and be able to add priority

@Ronald_01

I have not tested this (because I have no sample data)

But how about this...

Active projects =
IF (
    ISBLANK ( Placement[cxsrec__Start_date__c] );
    "No Start Date";
    IF (
        Placement[cxsrec__Start_date__c] <= TODAY ();
        IF (
            ISBLANK ( Placement[cxsrec__End_date__c] );
            IF (
                ISBLANK ( Placement[Expected_Project_End_Date_TMC__c] );
                IF (
                    ISBLANK ( Placement[End_Date_PO_Sub_agreement__c] );
                    "No End Date of any kind";
                    IF (
                        Placement[End_Date_PO_Sub_agreement__c] >= TODAY ();
                        "Active";
                        IF (
                            Placement[Expected_Project_End_Date_TMC__c] >= TODAY ();
                            "Active";
                            IF ( Placement[cxsrec__End_date__c] >= TODAY (); "Active"; "Not Active" )
                        )
                    )
                )
            )
        )
    )
)

Good Luck! Smiley Happy

 

EDIT: Obviously you can change the resulting text you don't really have to say "No End Date of any kind"

that's just for easier reading of the formula! Smiley Happy

Super User
Super User

Re: Combine multiple if statements and be able to add priority

I corrected a bunch of paren issues, etc. I think this makes it more clear what is going on.

 

// Create a variable "end_date" that takes on the correct 
// value depending upon what columns are blank

Active projects pseudo =

VAR end_date =

IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test IF(ISBLANK(Placement[Expected_Project_End_Date_TMC__c]); //second if test (true condition from first if) Placement[End_Date_PO_Sub_agreement__c]; Placement[Expected_Project_End_Date_TMC__c]
); //end second if test Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank
); //end first if test
// Return is based upon the value of this variable "end_date" RETURN
IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY();
"active"; //true condition
BLANK() //false condition
)

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Ronald_01 Regular Visitor
Regular Visitor

Re: Combine multiple if statements and be able to add priority

Hi @Greg_Deckler

 

We are really close. Thank you very much for helping out.

 

PO end date is more important than expected project end date.PNG

The only thing we are missing is that the PO end date is more important than the expected end date. So as we see in this example the PO end date is later than the expected end-date, this project should be categorised as active. 

 

Is there a way to take this into account?

 

Greetings Ronald

 

 

 

Super User
Super User

Re: Combine multiple if statements and be able to add priority

Sure, just flip the logic a little:

 

 

// Create a variable "end_date" that takes on the correct 
// value depending upon what columns are blank

Active projects pseudo = 

VAR end_date = 

   IF(ISBLANK(Placement[cxsrec__End_date__c]); //first if test

      IF(ISBLANK(Placement[End_Date_PO_Sub_agreement__c]); //second if test (true condition from first if)
         Placement[Expected_Project_End_Date_TMC__c]; //if PO End date is blank, use Project end date
         Placement[End_Date_Sub_agreement__c] //Otherwise, use the PO End Date
      ); //end second if test

      Placement[cxsrec__End_date__c] // false condition from first if, cxsrec__End_date__c is not blank
   ); //end first if test

// Return is based upon the value of this variable "end_date"
RETURN 
  IF(Placement[cxsrec__Start_date__c] < TODAY() && end_date >= TODAY(); 
     "active"; //true condition
     BLANK() //false condition
  )

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!