RYG formula based on completion time and status

Monika Tomczak

Hi, I would like to have a formula that shows me RYG status based on below criteria:

-if status = completed and [Actual Finish] = [Baseline Finish Date] then green

-if status work in progress (wip) or not started and [Actual Finish] is less than 14 past today then also green

-if status wip or not started and [Actual Finish] is between 14 and 28 days past today then yellow

--if status wip or not started and [Actual Finish] is more than 28 days past today then red

I built the below formula to reflect that but it doesn't work:

=IF(OR(AND(Status585=”completed”, [Actual Finish]585=[Baseline Finish Date]585), OR(and(Status585="wip", TODAY()-[Actual Finish]<14), Status585="not started",TODAY()-[Actual Finish]585 < 14)), “Green”, IF(AND(TODAY()-[Actual Finish]585<28,TODAY()-[Actual Finish]585>14),"Yellow","Red"))

Could You give me an idea what's wrong with it?

Thanks!

Genevieve P.

Hi Monika,

Try this:

=IF(OR(AND(Status@row = "completed", [Actual Finish]@row = [Baseline Finish Date]@row), AND(Status@row = "wip", TODAY() - [Actual Finish]@row < 14), Status@row = "not started", TODAY() - [Actual Finish]@row < 14), "Green", IF(AND(TODAY() - [Actual Finish]@row < 28, TODAY() - [Actual Finish]@row > 14), "Yellow", "Red"))

You didn't need to have the OR twice, since you already stated it at the beginning. I also noticed some of your quotation marks weren't formatted correctly, so if the column names don't light up in colours, delete out the quotes and type them directly into Smartsheet (if you're copying/pasting quotes from a notepad sometimes they're recognized as different characters so you'll need to make sure it's formatted as Smartsheet " marks).

You'll also notice that I replaced your row number (585) with the @row function. This will help your sheet process faster since it won't have to scan through your sheet to locate row 585 each time, but will just look at the content within the row.

Let me know if this is providing the expected output or not!

Cheers,

Genevieve

Genevieve P.

Hi Monika,

Try this:

=IF(OR(AND(Status@row = "completed", [Actual Finish]@row = [Baseline Finish Date]@row), AND(Status@row = "wip", TODAY() - [Actual Finish]@row < 14), Status@row = "not started", TODAY() - [Actual Finish]@row < 14), "Green", IF(AND(TODAY() - [Actual Finish]@row < 28, TODAY() - [Actual Finish]@row > 14), "Yellow", "Red"))

You didn't need to have the OR twice, since you already stated it at the beginning. I also noticed some of your quotation marks weren't formatted correctly, so if the column names don't light up in colours, delete out the quotes and type them directly into Smartsheet (if you're copying/pasting quotes from a notepad sometimes they're recognized as different characters so you'll need to make sure it's formatted as Smartsheet " marks).

You'll also notice that I replaced your row number (585) with the @row function. This will help your sheet process faster since it won't have to scan through your sheet to locate row 585 each time, but will just look at the content within the row.

Let me know if this is providing the expected output or not!

Cheers,

Genevieve

Monika Tomczak

Hi Genevieve,

Thanks a lot for Your support, that resolution works perfectly.

Genevieve P.

Hi Monika,

That's great to hear! I'm glad it works for you 😊