# 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:

1. When the date in "Finish" Column is within 14 days of date listed
2. When the percentage in the "% Complete" Column is less than 100%
3. 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

• 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)

Cut-paste 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 TRUE--when 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)

• 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)

Cut-paste 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 TRUE--when 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!