At Risk Flag

klawr95

Hi there! I am trying to have it so that the flag shows if the End Date is 3 days away AND the Status is "Not Started" (ideally, I would like it to show if the status is anything but Completed, but that may be too difficult). I know to use a nested IF/AND function, but I keep getting errors when I write it out. I started with: =IF(AND([End Date]@row < TODAY() - 3, Status@row = "Not Started", 1, 0)) and have been messing with that a little and still no luck. This is only a sample sheet I am presenting on Monday, that's why there aren't many dates in there. Thanks in advance!

Mike Wilday

Very close! You can use <> to mean does not equal, and you have to close the AND statement after your last criterion. Try this one:

=IF(AND([End Date]@row < TODAY() - 3, Status@row <> "Completed"), 1, 0)

Mike Wilday

Actually, I would also revise your today statement. You're today statement says less than today - 3 days ago... not three days into the future.... this one should meet that requirement too. 

=IF(AND([End Date]@row < TODAY(3), Status@row <> "Completed"), 1, 0)

It's looking at if your end date is less than 3 days from today. 

klawr95

So close, yet so wrong haha. Thank you so much!

klawr95

Is there a way to have it so if there is no date, no flag will show? That's what I thought the AND function was for. Thanks!

Andrée Starå

Hi,

Try something like this in the beginning.

=IF(NOT(ISDATE([End Date]@row)); 0; YOUR OTHER FORMULA

The same version but with the below changes for your and others convenience.

=IF(NOT(ISDATE([End Date]@row)), 0, YOUR OTHER FORMULA

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

klawr95

That did the trick!! Awesome, thanks for your help. 

Andrée Starå

Excellent!

Happy to help!

Mike Wilday

You're welcome. It looks like Andre answered your second question. Let us know if you need additional help!