Help with R-Y-G Foumula across multiple columns

[AK43040 (Adam)]

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:

[JCorrell (Jim - The Monday Man)]

@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.

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
Schedule a 1-on-1 Tutorial Session

[AK43040 (Adam)]

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?

[JCorrell (Jim - The Monday Man)]

@AK43040

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

[AK43040 (Adam)]

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,“”,“”))

[JCorrell (Jim - The Monday Man)]

@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, "🟠", "🟡")
)

[AK43040 (Adam)]

@JCorrell

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

@JCorrell this worked! You rock!

[AK43040 (Adam)]

[JCorrell (Jim - The Monday Man)]

@AK43040

Awesome. Glad to hear it!

---

Jim - The Monday Man (YouTube Channel) Our latest vid: Reading REALLY Large monday Boards
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or “Rocket Science”)