How to make the health column mark red if Actual End date> Today using the column formula

I have health column where I am managing the status of completed, in progress and planned with Green, Yellow and blue color respectively. Whereas I also nested the IF AND in same column if the Actual End Date> today and the status is On-Hold then the health column should be automictically be red. This is the formula.

=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red", IF(Status@row = "Planned", "Blue", ""))))

But the formula isn't working for "IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red"" this part. Any suggestions @Paul Newcome @Genevieve P.

Find more posts tagged with

Accepted answers

All comments

Hollie Green

Are you sure the greater than today is what you want to be Red. Only asking because I know I will type the incorrect greater than or less than symbol myself sometimes and it would make more sense for it to be Red if the date was less than today.

Ray Lindstrom

@moumita77,

=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Planned", "Blue", IF(AND(Status@row = "On-Hold", [Actual End Date]@row > TODAY()), "Red", ""))))

However this does not assign a color for On-Hold with a date less than today.

moumita77

Hey @Ray Lindstrom Thanks for guiding, this formula is working fine for In Progress, Planned and completed. But "if the actual date is beyond today and the task status is not completed then it should mark red". This condition i am unable to met. Kindly guide me for that segment.

moumita77

Hey @Hollie Green yes if actual date is more than today and the status is not completed then want the health column to show red other than that all the status have defined color.

Can you guide for same.

Hollie Green

@moumita77 Your formula looks correct to work for that can you provide a screenshot of your sheet with column headers and what it is doing for the "Red" part of your formula. I copied your formula exactly and it worked

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.