Flag Function
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.
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!?!?
-
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:
- 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%
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!