Program Health Formula

Stephen Braunewell

Hi all,

I am looking for a new health formula for my action item register. Ideally, it would have the following characteristics:

Symbol would be green if "Due Date" column had a date that was greater than or equal to 30 days away OR if "Completed" column was checked

Symbol would be yellow if "Due Date" column had a date that was greater than or equal to 15 days away but less than 30 days away and "Completed" column was not checked

Symbol would be red if "Due Date" column had a date that was less than 15 days away but and "Completed" column was not checked

Thanks in advance,

Stephen

Scott Peters

Hi @Stephen Braunewell - We do something similar in our project plan template to indicate when something is due. Here's our formula:

IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Closed", IF(ISBLANK([Due Date]@row), "Need Date", IF([Due Date]@row < TODAY(), "Overdue", IF([Due Date]@row < TODAY(7), "This Week", IF([Due Date]@row < TODAY(14), "Next Week", "Future")))))), "")

Here's how apply conditional formatting:

You could do something similar with a symbol type column, where the result of your if statement is R, Y, or G, you get one of these:

Hope that's helpful!