Nested If with Multiple Conditions Automation for Project Management

Kristina Howes

Hi SS Community! 

 

Had a complicated question and my formula keeps coming back unparseable, so I wanted to run this by you and see if anybody could help me. 

Currently, I have this: 

=IF(AND([Start Date]@row < (TODAY() + 14)), NOT(OR([Status]@row)="Complete"), ([Status]@row)= "In Progress"),"On Deck", "Not Started")

 

I just need to automate

If the start date is within 2 weeks and the status is not complete or in progress, then mark it as on deck. 

Otherwise, default to not started. 

Thanks for the advice in advance!  

 

L_123

You prematurely closed off your or statement and didn't close the not afterwards

=IF(AND([Start Date]@row < (TODAY() + 14)), NOT(OR([Status]@row="Complete", [Status]@row= "In Progress")),"On Deck", "Not Started")

L_123

=IF(AND([Start Date]@row < (TODAY() + 14), NOT(OR([Status]@row="Complete", [Status]@row= "In Progress"))),"On Deck", "Not Started")

 

You also prematurely closed your and statement. Missed that in the first answer.

Kristina Howes

Hmm, it says that it's a circular reference. Any way that I can fix this? I am putting this in the column titled "Status". 

 

Thank you!

L_123

You can't reference the current cell for a value, then change that value. I think your best solution is to add another column to have this work properly.