Red flag shown or nothing on certain criteria

Darren Maggs

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

Toufong Vang

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.

Toufong Vang

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)

Toufong Vang

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.

Darren Maggs

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?

Darren Maggs

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

Toufong Vang

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)