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

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,

Find more posts tagged with

Formulas

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
Sheet Summary Formula Help
Hello, I am trying to get this formula to work but am not having any luck. When I remove the last @cell….text below the formula works great. When I add it in I get an error. Any ideas? =COUNTIFS([ACCT READINESS STATUS PLV]:[ACCT READINESS STATUS PLV], OR(@cell = "PENDING LOCAL CAH", @cell = "ADDITIONAL REVIEW NEEDED LOCAL…
How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…