If/Or Statement Based on Criteria - REPOST

[Robby19]

I am trying to have a cell state Compliant or Non-Compliant based off of results in other cells. I need TRS Compliance to say Compliant if, EAS is greater than 6 months from today or the TRS Master = Y.

My current formula for TRS Compliance,
=IF(OR([@EAS]<=Table1[today + 6 months],[@[TRS Master]]=""),"Non-Compliant","Compliant")

MCTIMS - TRS through Date Submitted are all Index(Match) formulas.

EAS	MCTIMS - TRS	MCTIMS - CAPSTONE	TRS MASTER	DATE ATTENDED	DATE SUBMITTED	DAYS TO EAS	TRS COMPLIANCE
20180401			Y	5-Feb-18	12-Mar-18	88	Non-Compliant

<tbody>
</tbody>

 

[mikerickson]

I need TRS Compliance to say Compliant if, EAS is greater than 6 months from today or the TRS Master = Y.

In your posted example, the TRS Master = Y, yet the compliance says "non-compliant".

 

[Robby19]

Exactly. The statement that I have posted, for some reason, does not work when it has been put to the test against actual data. I am not sure if that is because the referenced cells are pulled from other sheets within the excel doc or what is wrong with it.

 

[mikerickson]

In the OP you say that you want "compliant" if
EAS is greater than Today+6 months
OR
TRS Master = Y

In your example, TRS Master = Y
so (EAS is greater than Today+6 month) or (TRS Master = Y) is TRUE
so one would expect "compliant"

Yet your example shows "not compliant".

I would use a formula like
=IF(OR([@EAS]>(TODAY()+DATE(0,6,0), [@TRS MASTER]="Y"), "Compliant", "other")

 

[Robby19]

This formula contains an error. When reviewing it in the Insert Formula function, the OR portion returns a value of Volatile. Excel will not let me input this into the spreadsheet.

 

[mikerickson]

It looks like I missed a parenthesis
=IF(OR([@EAS]>(TODAY()+DATE(0,6,0)), [@TRS MASTER]="Y"), "Compliant", "other")