Flag Function

Cara Sikes

I asked this before and thought that it was working -- but today I realized it is not.

This is the formula that I am using so that an item is flagged if it is not 100% complete and the complete by date is today or three days prior: =IF(AND([Complete By]@row >= TODAY(-3), [% Complete]@row < 1), 1, 0)

The complete by date on my item is 6/15/20 and it is flagged. Why is this happening? Clearly it is not an item in jeopardy.

Genevieve P.

Hi Cara,

Right now your formula isn't looking between two dates, it's just looking for dates that are in the future from a specific time without a stopping point. To adjust this, we can add in another condition that says "less than", so the formula knows when to stop. Perhaps stopping at TODAY?

Try this:

 =IF(AND([Complete By]@row >= TODAY(-3), [Complete By]@row <= TODAY(), [% Complete]@row < 1), 1, 0)

Let me know if this works or if you have any questions!

Cheers,

Genevieve

Cara Sikes

Genevieve - that doesn't seem to be working. None of the flags are popping now.

What I have is a task that has a date that it needs to be completed by (Complete By), a percentage complete column (% Complete), a column for status <i.e. Complete, In Progress, On Hold> (Status) and a column to enter the date that it was actually completed on (Completed On).

I need the flag to pop if a task has a Complete By date which occurs anytime in the next three days or is past the Complete By date AND the % Complete is not 100% (this could also work if the Status is not marked as Complete or the Completed On column is empty - whatever is best to make this work).

Help!?!?

Genevieve P.

Hi Cara,

Thank you for explaining a bit more!

The >= TODAY(-3) part of your formula would have been looking for anything in the future, starting from three days in the past (since the 3 is negative).

First, lets break down your two statements, or the two requirements for the flag to pop up:

1. If a task has a Complete By date which occurs anytime in the next three days
2. Or, is past the Complete By date AND the % Complete is not 100%

Based on this, it actually sounds like we could make your formula more simple. If we simply build a formula for your second statement, this will encompass your first statement (as I presume you don't want the flag if the % Complete is 100%?)

Let's go back to your very first statement from your initial question. I believe the only thing we need to change is the criteria around TODAY. Instead of using -3 which looks to 3 days in the past, let's use positive 3, which looks to three days from now. Then, we can use Less Than (<) instead of your greater than symbol.

The reason being that we're looking for less than 3 days in the future. Or, any dates either in the past, or within the next three days, that have not been marked as 100% complete.

Try this instead:

=IF(AND([Complete By]@row <= TODAY(3), [% Complete]@row < 1), 1, 0)

Let me know if this works! If not, we can use different criteria. It would also be helpful to see your sheet if you don't mind sharing a screen capture (but please block out any sensitive data).

Cheers,

Genevieve

Cara Sikes

That was it!! The only problem that I have now is that if the Complete By date is blank, the flag pops. Is there a way to fix that?

Genevieve P.

Hi Cara,

Yes, no problem! You can add different logic statements by nesting IF statements together. Keep in mind that logic statements read from left-to-right... so we want to put this blank statement first:

=IF([Complete By]@row = "", 0

This says that if the Complete By cell is blank, don't raise the flag. Otherwise... then we go into the next statement:

=IF([Complete By]@row = "", 0, IF(AND([Complete By]@row <= TODAY(3), [% Complete]@row < 1), 1, 0))

Does that make sense?

Cheers!

Genevieve