Task Health - Red, Yellow, Green

I am currently using =IF(TODAY() - [Due Date]@row < 0, "Green", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() - [Due Date]@row < [Processing Goal]@row, "Yellow"))) because I can't figure out how to get the yellow to work correctly.

I am looking for a formula that will display green if today is less than the due date, red if today is greater than the due date and yellow if today + 1/2 the processing goal is less than the due date. If the processing goal is 10 days, I want the health to turn yellow when we are 5 days from the due date so we can assign additional resources before we reach the due date. Can anyone help?

Find more posts tagged with

Accepted answers

Paul Newcome

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

All comments

Monique Odom-Stearn

Hello @blmccue!

Try the following formula:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY([Processing Goal]@row / 2) < [Due Date]@row, "Green", "Yellow"))

blmccue

Hi @Monique_Odom_Comcast, I had tried that once and tried again but receive and invalid data type error.

blmccue

Wondering if anyone else has any suggestions?

I've also added a helper column that calculates 1/2 the processing goal to see if that would help fix the errors

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(Helper@row) < [Due Date]@row, "Green", "Yellow"))

Paul Newcome

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

DKazatsky2

Hi @blmccue,

The issue may be in trying to use a decimal inside TODAY(), that causes an "#invalid data type" error.

Hope that helps,

Dave

blmccue

@Paul Newcome, that worked!

Greatly appreciated.

Paul Newcome

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.