Struggling with IF/AND and colors

Options

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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    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 ✭✭✭
    Options

    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?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

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

  • lmarantos
    lmarantos ✭✭✭
    Options

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 02/09/23
    Options

    Switch it to Date and the formula should work.

  • lmarantos
    lmarantos ✭✭✭
    Options

    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!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!