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

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

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

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

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

lmarantos

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

Nick Korna

Switch it to Date and the formula should work.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.