Health Formula

Amy F

I need to build a health formula that is based on the "Target End Date" and "Status" columns.

If the status is "Not Started" or "In Progress" and the target end date is >=today +1 then "yellow", but if it is MORE than today +3 then "red" otherwise leave as "green" Any thoughts? I'm sure and IF(AND combo is needed, just not sure how to put it all together.

ADDED: I also need the items completed to retain the RYG rather than all being green when marked as complete.

Ryan Sides

@Amy F

Hi Amy,

try this one:

=IF(OR(Status@row = "In Progress", Status@row = "Not Started"), IF([Target End Date]@row <= TODAY(), "green", IF([Target End Date]@row <= TODAY() + 3, "yellow", "red")), "green")

Let me know if that one works for you.

Ryan

Amy F

@Ryan Sides Thank you! With a little tweaking, that got me pretty close. I can't seem to make it pick up the red for anything overdue by 3+ days:

=IF(OR(Status@row = "In Progress", Status@row = "Not Started"), IF([Target End Date]@row >= TODAY(), "Green", IF([Target End Date]@row <= TODAY() + 1, "Yellow", IF([Target End Date]@row <= TODAY() + 3, "Red"))), "Green")

I also need the items completed to retain the health rather than all being green when marked as complete.

Ryan Sides

Your greater than and less than signs don't match the ones I sent previously. Can you check them?

It will not be able to maintain the health because the formula relies on the status. It needs to know what to do when marked as complete. It can't look at itself for the answer. Does that make sense?

Amy F

@Ryan Sides Thank you for replying. It was a tremendous help! I finally got it to work with a little more manipulation.

=IF(OR(Status@row = "In Progress", Status@row = "Not Started"), IF([Target End Date]@row >= TODAY(), "Green", IF([Target End Date]@row <= TODAY(-3), "Red", "Yellow")), "Green")

Is there a way to retain the RYG even when marked complete?

Genevieve P.

Hi @Amy F

Since you're using the TODAY function, this will update the formula every day (when you open the sheet). This means that if you were to use the formula for the "Complete" row, they would all eventually turn Red since the End Date would be in the past, eventually.

What you could do is set up a new Date column to record exactly when the Status changes to "Complete" (see: Set the current date with the Record a Date action). Then you could use this date as the comparison in your IF statement, when the Status is complete.

Cheers,

Genevieve