cancel
Showing results for
Did you mean:
Ronald_01 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

## 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

## 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

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

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

## 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

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

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

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

@Ronald_01

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

```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!  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 blankActive 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

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

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 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!