RAG Status formula scenario

Avinash Chandra

Hi Smartsheets wizards,

Good Evening!

I have created a formula that has been working great, however I find that if the actual finish date is missing for any reason the RAG does not change to RED. Here is the formula and the screenshot of the schedule. Kindly advise what I should change to get the RAG to change to red if the actual date is missing for any reason. Look at the date 8th Feb 2023 row.

schedule dates.JPG

=IF(OR(AND([Trending Start]@row < TODAY(), [Actual Start]@row = ""), AND(([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", IF(OR(AND(([Actual Start]@row - [Trending Start]@row) >= 10, [% Complete]@row < 0.5), AND([Actual Finish]@row > [Trending Finish]@row, [% Complete]@row <= 0.5)), "Yellow", IF(AND([% Complete]@row = 1, [Actual Finish]@row <> ""), "Blue", "Green")))

Julie Fortney

Hi @Avinash Chandra,

Here's what I've interpreted from your formula:

Red

trending start is in the past and there is no actual start date

less than 10% complete and actual start is greater than or equal to 20 days after trending start

Yellow

less than 50% complete and actual start is great than or equal to 10 days after trending start

less than 50% complete and actual finish is after trending finish

Blue

task is complete and actual finish is not blank

Otherwise Green

Could you confirm that you in fact want the health to be red in any row that is missing an Actual Finish date? If so, you could add that to the beginning of your formula.

www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

Avinash Chandra

What is happening: People are copying and pasting Trending start date into actual start date and missing out filling actual finish date. RAG remains Green. It should change to RED.

How do I add double condition to this: (([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", AND Actual finish date is blank.

Kindly advise.

Julie Fortney

Based on the formula you posted above, you want to add the condition as shown in bold italics below, correct?

Red

trending start is in the past and there is no actual start date

less than or equal to 10% complete, actual start is greater than or equal to 20 days after trending start, and Actual Finish is blank

Yellow

less than 50% complete and actual start is great than or equal to 10 days after trending start

less than 50% complete and actual finish is after trending finish

Blue

task is complete and actual finish is not blank

Otherwise Green