How to use All 4 Formulas together "IF AND NOT OR "

Mohammed Sulaiman

I have to create a formula that returns a value "1 or 0", so I use the IF statement. Now to satisfy multiple conditions, I am using the AND statement. 

Also, to I want to check if the statement is not true, Hence I use the NOT function. 

Based on this, the formula for my example is: 

=IF(AND([Actual Complete]6 < TODAY(), NOT(Status6 = "Complete")), 1, 0)

Now I want to modify my formula such that : 

The status could be either "Complete" or "Completed Late", how do I change the formula. 

 

Thanks, 

 

Mike Wilday

Without actually testing this out myself... try this and let me know if it works? 

=IF(AND([Actual Complete]6 < TODAY(), OR(NOT(Status6 = "Complete"), Not(Status6 = "Completed Late"))), 1, 0)

Mohammed Sulaiman

No it does not work

Mike Wilday

What about something like this? Which searches for the word complete in the status row. I set up the formulat to search the row it is in. But you could always replace @row with an actual row number. 

=IF(AND([Actual complete]@row < TODAY(), FIND("Complete", status@row) > 0), 1, 0)

Mohammed Sulaiman

It doesn't work, Returns as "Unparseable". 

 

Could I be doing something wrong ?

Mike Wilday

Is your Actual Date column, a date column?