If / OR statement

[WillSTARK]

Hi,

I have various columns including column F, J (both have dates values or no value/no dates) and U (which has a text value)

I want to say that if at least one of the columns meet my criteria then, it shows the row as Active / Inactive. However, the function below only gives me inactive even when it should be sometimes shown as Active.

=IF(OR(F2 <TODAY(),or((J2<TODAY()),or(U2 = "Terminated"))),"Inactive","Active")

Do you have any idea / explanations?

 

[FormR]

Hi, your logical tests should probably all be within one OR() function. i.e.

=IF(OR(F2 < TODAY(),JJ2 < TODAY(),U2="Terminated"),"Inactive","Active")<today(),jj2<today(),u2="terminated"),"inactive","active")< html=""></today(),jj2<today(),u2="terminated"),"inactive","active")<>

 

[WillSTARK]

Hi,

Thks for the suggestion. It sill shows Inactive when it should be Active. It does so when the rows are blanks. Columns F and J are dates, so perhaps it considers a blank cell as 0 which is < to today's date. I tried this Formula adding <>"" but it does not work. It might consider it as a text string and not a number.

=IF(OR(and(F2<TODAY(),<>""),and(J2<TODAY(),<>""),U2="Terminated"),"Inactive","Active")

Example

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

	F	J	U	Expected
	Terminated	Inactive
	Inactive
		Terminated	Inactive
		Inactive
			Active
		Inactive

<colgroup><col style="width: 120px"><col width="120"><col width="120"><col width="120"><col width="120"></colgroup><tbody>
[TD="align: right"]1[/TD]
[TD="align: right"]12/12/2012[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]12/12/2012[/TD]
[TD="align: right"]10/10/2013[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]12/12/2012[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]12/12/2012[/TD]

</tbody>

 

[FormR]

Hi, you appear to be missing some cell reference, see red addtions:

=IF(OR(and(F2<TODAY(),F2<>""),and(J2<TODAY(),J2<>""),U2="Terminated"),"Inactive","Active")

 

[WillSTARK]

Correct, it works. Thanks.