Struggling with IF/AND and colors

Hi there!

I am trying to assign colors in a column based on the number of days an employee has been in the role:

Red for less than 90 days

Yellow for 90-180 days

Green for 181-365 days

Blue for over 365 days

Here's what I've been trying but I just can't get it to work... I've looked through several posts but I still need help! Thank you for your time and expertise!

=IF([Time]@row <= TODAY(90), "Red", IF(AND([Time]@row > TODAY(90), [Time]@row < TODAY(180), "Yellow")), IF(AND([Time]@row > TODAY(180), [Time]@row < TODAY(365), "Green")), IF([Time]@row >= TODAY(365), "Blue"))

Lindsey Marantos, PhD

Talent Programs Manager, Total Rewards
Heartland Dental

Answers

  • Hi @lmarantos

    You're very close! We'll want to make sure we close off each AND statement before saying if it's "Yellow" or "Red", etc.

    We also just need to adjust your criteria a little bit... for example, instead of saying:

    =IF([Time]@row <= TODAY(90), "Red", 

    Which will mark anything less than 90 days in the future Red, including all items in the past, try:

    =IF([Time]@row >= TODAY(-90), "Red", 

    This says, if the date is greater than 90 days ago, as in if it was 10 days ago that they were hired, or 80 days ago, then it's "Red".


    Try this:

    =IF(Time@row >= TODAY(-90), "Red", IF(AND(Time@row <= TODAY(-90), Time@row >= TODAY(-180)), "Yellow", IF(AND(Time@row <= TODAY(-180), Time@row >= TODAY(-365)), "Green", IF(Time@row <= TODAY(-365), "Blue"))))


    Cheers,

    Genevieve

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • Nick Korna
    Nick Korna Community Champion

    Hi @lmarantos ,

    There is no need for the ANDs here, you can simply do it with a nested IF statement like this:

    =IF(Time@row > TODAY(-90), "Red", IF(Time@row >= TODAY(-180), "Yellow", IF(Time@row >= TODAY(-365), "Green", "Blue")))

    Outcome:

    If you may potentially have the Time column empty and don't want blank values showing up as blue:

    =IFERROR(IF(NOT(ISBLANK(Time@row)), IF(Time@row > TODAY(-90), "Red", IF(Time@row >= TODAY(-180), "Yellow", IF(Time@row >= TODAY(-365), "Green", "Blue")))), "")

    Will give you results like:

    Is the kind of outcome you're looking for? Let me know! 😊

  • lmarantos
    lmarantos ✭✭✭✭

    Hi @Nick Korna !

    This seems like it should work (the first one seems great because there will always be a value in Time), but when I copied it and when I typed it in myself it said invalid both times... any thoughts?

    Lindsey Marantos, PhD

    Talent Programs Manager, Total Rewards
    Heartland Dental

  • Nick Korna
    Nick Korna Community Champion

    What kind of invalid result are you getting? Is your Time column set to "Date"?

  • lmarantos
    lmarantos ✭✭✭✭

    @Nick Korna it says invalid operation and no, the time column is text/number :-/

    Lindsey Marantos, PhD

    Talent Programs Manager, Total Rewards
    Heartland Dental

  • Nick Korna
    Nick Korna Community Champion
    edited 02/09/23

    Switch it to Date and the formula should work.

  • lmarantos
    lmarantos ✭✭✭✭

    I had made a formula for the calculated time from a Hire Date column, so I couldn't make the time column a date column. But, I changed the Time column in the formula to the Hire Date column and it works beautifully :) Thank you SO MUCH!!

    Lindsey Marantos, PhD

    Talent Programs Manager, Total Rewards
    Heartland Dental

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!