RYGB health changes on date and status

Angela.R

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow", "Green")))))

Goal: Health RYGB balls.

-red, end date past due in any status

-yellow, start date <=6days of today, status not started

-blue - status is complete

-green if all is good.

Issue: the ball will not turn Green unless within 6 days of today in a status of in progress.

I've tried to reorder the criteria, but then cannot get the formula to work at all.

Any help welcome!

Genevieve P.

Hi @Angela.R

This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

For example, instead of:

IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

Try:

IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

Full Formula:

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow", "Green"))))

Cheers,

Genevieve

Genevieve P.

Hi @Angela.R

This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

For example, instead of:

IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

Try:

IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

Full Formula:

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow", "Green"))))

Cheers,

Genevieve