Help with R-Y-G Foumula across multiple columns

How can I apply a formula like below so that it references the column with the last date added running left-to-right? Meaning replace {Stage Date} with columns {Intial MMS}, {Survey}, {Final MMS}, and {Qualified Win} so that the formula reads left-to-right across the date columns and uses the date furthest to the right of the four columns as it’s reference date for the formula and R-Y-G outcome? Typically would be thinking of an INDEX/MATCH/MAX formula if in Excel.

Current Formula used that works well with a single date column reference:
IF(WORKDAYS(TODAY(),{Stage Date})<=10,“”,IF(WORKDAYS(TODAY(),{Stage Date})>=15,“”,“”))

Sample of board with highlights to illustrate concept:

@AK43040

I would recommend creating an interim formula column:
`IF({Date4}, {Date4}, IF({Date3}, {Date3}, IF({Date2}, {Date2}, IF({Date1}, {Date1}, ""))))`

Then point your first formula to the new formula.

1 Like

Hello @JCorrell !

I attempted this and resulted in an error.

This is the IF formula and it works just fine: IF({Qualified Win }, {Qualified Win }, IF({Final MMS}, {Final MMS}, IF({Survey }, {Survey }, IF({KM360}, {KM360}, “”))))

However when I point the formula to the column with the above formula it results in an error:
IF(WORKDAYS(TODAY(),{Stage Date})<=10,“”,IF(WORKDAYS(TODAY(),{Stage Date})>=15,“”,“”))

Stage Date = new IF formula in a formula column.

What am I missing?

@AK43040

Please give more details… does it always give an error, what is the error?

The error is an “illegal formula” error.

The formula you provided is working fine. Here it is formatted to my column headers:

IF({Qualified Win }, {Qualified Win }, IF({Final MMS}, {Final MMS}, IF({Survey }, {Survey }, IF({KM360}, {KM360}, “”))))

I have a seperate column with this formula pointing to the column containing the above formula:

IF(WORKDAYS(TODAY(),{Last Stage Date})<=10,“”,IF(WORKDAYS(TODAY(),{Last Stage Date})>=15,“”,“”))

@AK43040

I got an error too. I didn’t figure out why. This works:

``````IF(WORKDAYS(TODAY(), {Last Stage Date}) <= 10,
"🟢",
IF(WORKDAYS(TODAY(), {Last Stage Date}) >= 15, "🟠", "🟡")
)
``````
1 Like

@JCorrell this worked! You rock!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.