Right now this formula works perfectly:
=IFERROR(IFS(AND(OR(I2="X",J2="X",K2="X",L2="X"),M2>=$A$2),"Completed",H2="","Unclaimed"),"In Progress")
I'd like to add one more condition.
Column M is the date we completed a task. Sometimes a team member will forget to add the date completed into column M. If there is an X in columns I, J, K, OR L, and Column H is NOT blank, but column M is blank, I'd like to return the value "<-- Missing date"
In the attachment, cell N4 should return the value "<-- missing date"
I tried this formula, and it works! Except this one doesn't return the value "Completed" when there's an X in columns I, J, K, OR L.
=IFERROR(IFS(H2="","Unclaimed",AND(OR(I2="X",J2="X",K2="X",L2="X"),M2=""),"<-- Missing date",AND(OR(I2="X",J2="X",K2="X",L2="X")*H2="*",M2>=$A$2),"Completed"),"In progress")
Bookmarks