Need some assistance on using "IF(AND) logic. I'm trying to render a "Red" "Yellow" or "Green".

I'm trying to render a "Red" "Yellow" or Green" solution from more than one criterial. Formula reads this way.

=IF(AND([% Complete]25 < "1", [End Date]25 > TODAY()), "Red", IF(AND([% Complete]25 < "1", [End Date]25 = TODAY()), "Yellow"), IF(AND([% Complete]25 = "1", [End Date]25 = TODAY()), "Green"))

Essentially I want to reference the "% Complete" column. If the completion is less than 1 (percentage format) then read the "end date" column. If the "end date" date is before the current date "TODAY()" then select "Red". If the "end date" date is = to the current date "TODAY()" then "Yellow". If the "end date" date is before the current date "TODAY()", then "Green"

In other words, if the task is 100% complete then it stays "Green" regardless. If the task is less than 100% complete then read the "end date" column to see if the task still has time to be completed (Green), is at risk (TODAY() = "end date"), (Yellow), if the current date has passed the "end date" and less than 100% complete, then (Red).

Find more posts tagged with

Accepted answers

Paul Newcome

@Joe Liotta Makes sense why you would use it then.

The way I think of dates is in numbers. Today = 0. So dates in the future would be positive numbers and dates in the past would be negatives. So if a date is less than today it is in the past and greater than today is in the future. Using this thought process makes it easier (for me at least) to use the TODAY function when I want to say something along the lines of five days in the future TODAY(5) or five days in the past TODAY(-5).

So if someone wants to check for a date being less than 5 days away... [Date Column]@row < TODAY(5).

In the past 7 days, you would be looking for a number between 0 (TODAY()) and -7 (TODAY(-7))... AND(@cell >= TODAY(-7), @cell <= TODAY())

It is very much like your logic of using NETDAYS to generate a number, but without the added function which throws in extra parenthesis which we all know can be a pain.

@Sy Gibbs Happy to help! 👍️

In all reality, your formula technically would function referencing the [% Complete] column so long as you removed the quotes from around your numbers. The only thing you would really need to change to accomplish exactly what you wanted would be to change the last AND to an OR.

All comments

Joe Liotta

You don't need the parenthesis around the 1 and it might be better to use NETDAYS() to compare the current date to the end date. Try this:

=IF(OR([% Complete]@row=1, NETDAYS(TODAY(), [End Date]@row)>1), "Green", IF(NETDAYS(TODAY(), [End Date]@row)<0, "Red", "Yellow"))

Note: If TODAY() and [End Date] are the same, NETDAYS() returns 1 and, if TODAY() is after [End Date], a negative value is returned from NETDAYS().

Paul Newcome

Another shorter option without using NETDAYS (as I am unsure what the benefit of that would be):

=IF(OR([% Complete]@row = 1, [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Yellow", "Red"))

Joe Liotta

I only suggested NETDAYS() because I know that it works from previous experience and I wasn't familiar with using logics with dates. I'm glad that I learned something new today. Thanks, Paul!

Sy Gibbs

Thanks for the feedback folks! I ended up finding my syntax error just minutes after I sent the request. Sometimes we get in tunnel vision mode when working through things like this. I appreciate the suggestions.

Paul Newcome

@Joe Liotta Makes sense why you would use it then.

So if someone wants to check for a date being less than 5 days away... [Date Column]@row < TODAY(5).

In the past 7 days, you would be looking for a number between 0 (TODAY()) and -7 (TODAY(-7))... AND(@cell >= TODAY(-7), @cell <= TODAY())

It is very much like your logic of using NETDAYS to generate a number, but without the added function which throws in extra parenthesis which we all know can be a pain.

@Sy Gibbs Happy to help! 👍️

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.