How do I un-flag automatically?

KBSheetBI

I am wondering how to unflag in my sheet automatically

I need it to flag when start date and/or finish date is in the past and status is not "completed"

I then also need it to un-flag when the status column is changed to completed or rejected

Anyone know of a formula?

John Shane

@KBSheetBI

A nested IF statement is probably the best route for formula, but some conditional formatting could work as well. Can you provide more information on what you mean by "flag" or a screenshot for reference?

Cheers,

John

KBSheetBI

@John Shane conditional formatting wont work because I cannot filter by color. Has to be a flag

John Shane

@KBSheetBI

Nested IF definitely seems to be the way to go. What are the names of the columns you are needing to reference?

KBSheetBI

@John Shane Start date, finish date, status

Then we I have the two flag columns above

John Shane

@KBSheetBI

This should work for the Late Start column. You will need to change [Start Date] to [Finish Date] for the Late Finish column.

= IF(OR(Status@row = “Completed”, Status@row = “Rejected”), 0, IF(AND(Status@row <> “Completed”, [Start Date] < TODAY()), 1, 0))

Cheers,

John