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
-
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! 😊
-
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 -
What kind of invalid result are you getting? Is your Time column set to "Date"?
-
@Nick Korna it says invalid operation and no, the time column is text/number :-/
Lindsey Marantos, PhD
Talent Programs Manager, Total Rewards
Heartland Dental -
Switch it to Date and the formula should work.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!