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

Options

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.

=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

• ✭✭✭✭✭✭
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! 😊

• ✭✭✭
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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

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

Switch it to Date and the formula should work.

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