Combine multiple if statements and be able to add priority

[Anonymous]

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!

[Greg_Deckler]

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())

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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
  )

[Anonymous]

@Greg_Deckler  Hello Greg, and others, 

I have a similar yet little different problem, I'd like to do the following:

               Column 1       Column 2        Resulting Column

Row 1     Names 1         Names 2        Check if 'column 1' contains a list of names, if yes then return that name, else check 'column                                                                2' if  it contains the same list of names, if yes then return that name, else return "NA"

How do I go about this syntax where I have to check each cell if it contains multiple values referencing from a diferrent table( I can also add the namses since they're not that many) ? 

Thanks in Advance

[Anonymous]

Hi @Greg_Deckler, 

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

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

[Greg_Deckler · Accepted solution]

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
  )

[Sean]

@Anonymous

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!

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!