Need Help With Color Coded Health Formula

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!

Answers

  • acline
    acline
    edited 02/08/24

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


  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!