Red flag shown or nothing on certain criteria
Hi all,
I am trying to get a Column named "At Risk" (Set as Symbol Only currently) to go red or nothing when:
 When the date in "Finish" Column is within 14 days of date listed
 When the percentage in the "% Complete" Column is less than 100%
 When the date in the "Finish" column has expired
I am having heaps of issues trying to get the formula to display the flag in these conditions only.
Darren
Best Answers

Hi, Darren.
This formula will meet your requirements.
=IF(OR(IFERROR(NETDAYS(Start@row, Finish@row) <= 14, 0), [% Complete]@row < 1, IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0)
Here's how I went about it...
Below are the statements for the 3 criteria you listed.
1. IFERROR(NETDAYS([Start]@row,[Finish]@row)<=14,0)
(Use IFERROR() to prevent NETDAYS() from blowing up when neither date exists.)
2. [% Complete]@row < 1.0
3. IFERROR(NETDAYS(TODAY(),[Finish]@row)<=1,0)
You want a red flag if any of the 3 is TRUE. To do this, we'll use the IF() and OR() functions.
= IF( OR(Condition1, Condition2, Condition3), 1, 0)
Cutpaste the statements into the formula...
=IF(OR(IFERROR(NETDAYS(Start@row, Finish@row) <= 14, 0), [% Complete]@row < 1, IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0)
Done!
However...the flag will always be red until #2 is no longer TRUEwhen a task is no longer less than 100% complete. You may want to include an exception for #2 to accommodate times when the finish date for a task is far into the future.

I see...try this
=IF([% Complete]@row = 1, 0, IF(OR(IFERROR(NETDAYS(Finish@row, Start@row) <= 14, 0), IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0))
I'm assuming that with, When the date in "Finish" Column is within 14 days of date listed, occurs when someone has 14 or less days to complete a task from Start date to Finish date. (See Row 1)
Answers

Hi, Darren.
This formula will meet your requirements.
=IF(OR(IFERROR(NETDAYS(Start@row, Finish@row) <= 14, 0), [% Complete]@row < 1, IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0)
Here's how I went about it...
Below are the statements for the 3 criteria you listed.
1. IFERROR(NETDAYS([Start]@row,[Finish]@row)<=14,0)
(Use IFERROR() to prevent NETDAYS() from blowing up when neither date exists.)
2. [% Complete]@row < 1.0
3. IFERROR(NETDAYS(TODAY(),[Finish]@row)<=1,0)
You want a red flag if any of the 3 is TRUE. To do this, we'll use the IF() and OR() functions.
= IF( OR(Condition1, Condition2, Condition3), 1, 0)
Cutpaste the statements into the formula...
=IF(OR(IFERROR(NETDAYS(Start@row, Finish@row) <= 14, 0), [% Complete]@row < 1, IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0)
Done!
However...the flag will always be red until #2 is no longer TRUEwhen a task is no longer less than 100% complete. You may want to include an exception for #2 to accommodate times when the finish date for a task is far into the future.

Thanks for your reply. The formula presents in the "At Risk" Column as a 1 instead of the "Red" flag.
Should I insert "Red" iin the formula somewhere to get it to accept this requirement?

Heres the formula I have in the cell now and it works but with a Zero (0) if it doesnt meet any of the 3 items.
=IF([% Complete]@row = 1, 0, IF(Finish@row  TODAY() <= 7, "Red", 0))
Darren

I see...try this
=IF([% Complete]@row = 1, 0, IF(OR(IFERROR(NETDAYS(Finish@row, Start@row) <= 14, 0), IFERROR(NETDAYS(TODAY(), Finish@row) <= 1, 0)), 1, 0))
I'm assuming that with, When the date in "Finish" Column is within 14 days of date listed, occurs when someone has 14 or less days to complete a task from Start date to Finish date. (See Row 1)
Help Article Resources
Categories
Check out the Formula Handbook template!