Health status in a project plan

Yaya

Hi,

I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green based on Status and Due Date.

If the status is not complete and the due date is in the past more than 5 working dates days, then yellow.

If the status is not complete and the due date is in the past more than 10 working days, then red.

If the status is blocked or on hold, flag it as red.

Otherwise, it displays Green.

Would very much appreciate a quick response!

Many thanks

Yaya

Genevieve P.

Hi @Yaya

Sure! This actually simplifies things.

If you put the first statement, that Status = "Complete" at the front, it means we can take out the statements that say "Status is not Complete" because the formula will only move on to the next statements if the first is false.

=IF(Status@row = "Complete", "Gray",

IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow",

IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red",

IF([Due Date]@row < TODAY(), "Yellow",

"Green"))))

Side note - I realized I had the data within the NETWORKDAYS function backwards and swapped it around. This should work better for you!

Cheers,

Genevieve

Genevieve P.

Hi @Yaya

I'd be happy to help you with this. I'll spell out each of the separate IF statements based on your conditions in the best order, then post the full formula at the bottom.

IF formulas stop as soon as they've found a correct condition, so we'll start with the RED statements:

• If the status is blocked or on hold, flag it as red.

=IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red"

• If the status is not complete and the due date is in the past more than 10 working days, then red.

IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 10), "Red"

• If the status is not complete and the due date is in the past between 6 - 10 working days, then yellow.

IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 5), "Yellow"

• Otherwise, it displays Green. (Note that this means any task with a due date in the past up to 5 days late will show Green)

, "Green"

Full Formula:

=IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 10), "Red", IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 5), "Yellow", "Green")))

Helpful Articles:

NETWORKDAYS Function / OR Function / AND Function / IF Function / Create Efficient Formulas with @cell and @row

If this isn't giving you the output you're looking for, it would be helpful to see a screen capture with example data. Cheers,

Genevieve

Yaya

@Genevieve P

Thanks for this. Upon reflection, can we rephrase it like the following logic like this?

If the status is complete, Gray.

If the status is not complete and the due date is in the past, but less than than 5 working dates days in the past , then yellow.

If the status is not complete and the due date is more than 5 working days in the past more, then red.

If the status is blocked or on hold, flag it as red.

For fields, where there is no start nor due date, then mark it as yellow.

Is this ok?

Thanks a million!

Genevieve P.

Hi @Yaya

Sure! This actually simplifies things.

If you put the first statement, that Status = "Complete" at the front, it means we can take out the statements that say "Status is not Complete" because the formula will only move on to the next statements if the first is false.

=IF(Status@row = "Complete", "Gray",

IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow",

IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red",

IF([Due Date]@row < TODAY(), "Yellow",

"Green"))))

Side note - I realized I had the data within the NETWORKDAYS function backwards and swapped it around. This should work better for you!

Cheers,

Genevieve

Yaya

Amazing!! Thanks so much!

Genevieve P.

No problem 🙂

Yaya

One final question - on the top on general information. Any way to exclude these cells being part of the formula or any other workaround you'd recommend?

Genevieve P.

Hi @Yaya

When you say "these cells", do you mean any of your Parent rows? So you would like to exclude anything that does not have hierarchy above it?

If so, we can add a statement in the beginning that checks for this and returns blank or "" if the current row has no ancestors:

=IF(COUNT(ANCESTORS(Activity@row)) = 0, "",

so:

=IF(COUNT(ANCESTORS(Activity@row)) = 0, "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

Yaya

@Genevieve P. just the top two general information lines. Any suggestions?

Genevieve P.

Hi @Yaya

I'm not sure what lines you mean, but if you only need to exclude two rows you could add in the IF statement at the beginning like before that returns blank if a certain identifier cell has specific text... such as "Note":

=IF(CONTAINS("Note", Status@row), "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

or

=IF(CONTAINS("specific text", Activity@row), "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

Yaya

This is amazing @Genevieve P. !!!