At Risk Flag

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!

Screen Shot 2019-11-08 at 3.40.08 PM.png

Find more posts tagged with

Non-profit

Formulas

Support

Comments

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!

Annotation 2019-11-11 082906.jpg

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…