Need Help With Color Coded Health Formula

Matt RobertP

Hello, I need some help getting color coded health status boxes to work for showing the status of fabrication work in rows. I want to use red for when the FAB STATUS % COMPLETE column is NOT 100% within two weeks of the NEED ON SITE DATE column. Yellow for when FAB STATUS % COMPLETE column is NOT 100% within two weeks of the NEED ON SITE DATE column. And finally green for when FAB STATUS % COMPLETE is NOT 100% within six weeks OR MORE from the NEED ON SITE DATE. I also want it to override these rules and make it Green no matter what if FAB STATUS % COMPLETE is 100% complete. Below are the actual names of the columns to help with writing the formula.

NEED ON SITE DATE

Fab Status % Complete (Needs Real Numbers)

Health Status For Fab Completion

"Health status for fab completion" is the Column where I am inputting the formula. I have it set to symbols with red, green, and yellow.

Thanks in advance to anyone that can help!

acline

I think this is what you want. Just set this as your column formula. If % Complete is blank, it is considered 0%

=IF([Fab Status % Complete]@row = 100, "Green", IF([NEED ON SITE DATE]@row < TODAY(15), "Red", IF([NEED ON SITE DATE]@row < TODAY(43), "Yellow", "Green")))

The IF statements work like this.

=IF(*this* is true, then do *this*, if not then do *this*)

=IF(logical_test, value_if_true, value_if_false)

If the status is 100, it's always green, otherwise, it will go to the second part.

IF([NEED ON SITE DATE]@row < TODAY(15), "Red", IF([NEED ON SITE DATE]@row < TODAY(43), "Yellow", "Green"))

If the date is less than TODAY+15, then the date is less than two weeks from now, then make the cell "Red".

If that is not the case, we check to see if the date is within 6 weeks using:

IF([NEED ON SITE DATE]@row < TODAY(43), "Yellow", "Green")

This will make the cell "Yellow" if the date is less than TODAY+43, otherwise the data is greater than 6 Weeks and we make it "Green"

You can play around with the 14/15 and 42/43 values to get what you want, but if it's a day off it's not that big of a deal.

If you want only business days, you could replace the [NEED ON SITE DATE]@row < TODAY(15) with:

NETWORKDAYS(start_date,end_date,[holidays]) -> NETWORKDAYS(TODAY(), [NEED ON SITE DATE]@row) < 10

This will check to see if the due date is within 2 working weeks (5-day weeks)

That comes out to:

=IF([Fab Status % Complete]@row = 100, "Green", IF(NETWORKDAYS(TODAY(), [NEED ON SITE DATE]@row) < 10, "Red", IF(NETWORKDAYS(TODAY(), [NEED ON SITE DATE]@row) < 30, "Yellow", "Green")))

Matt RobertP

I got it to work with this formula

=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), "Red", "Yellow"))

Thanks for your help @acline