Red, Yellow and Green Countdown forumula

Julie Still

I would like to add Red, Yellow and Green to help visualize how many days until the event has been closed.

Paul Newcome

It's the column names. Having the parenthesis in the column name threw me off a little. My apologies.

Try this...

=IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row, TODAY() >= [Closed Date (35 days after End Date)]@row - 5), "Red", IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row - 10, TODAY() >= [Closed Date (35 days after End Date)]@row - 20), "Yellow", IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row - 21, TODAY() >= [Closed Date (35 days after End Date)]@row - 34), "Green")))

Ryan Kramer

@Julie Still

You could create a formula that would look at two date fields and conditional render a status next to the field that you want to highlight as shown above.

Something like if Field B - Field A < 35, red

Ryan

Julie Still

I already have a formula under Closed date:

= [Closed Date] - [End Date] <35, red

Julie Still

Trying to have a red, yellow or green symbol in the middle column. Is this possible?

Ryan Kramer

@Julie Still ,

Of course it is. Just make the column of type symbol and then add your if statements.

If you're stuck, just share the grid with me - ryan@dynamicduo.io

Ryan

Paul Newcome

Try something like this...

=IF(AND(TODAY() <= [Closed Date (35 days after End Date]@row, TODAY() >= [Closed Date (35 days after End Date]@row - 5), "Red", IF(AND(TODAY() <= [Closed Date (35 days after End Date]@row - 10, TODAY() >= [Closed Date (35 days after End Date]@row - 20), "Yellow", IF(AND(TODAY() <= [Closed Date (35 days after End Date]@row - 21, TODAY() >= [Closed Date (35 days after End Date]@row - 34), "Green")))

Julie Still

@Paul Newcome and @Ryan Kramer ,

I must be missing a step. I copied Paul's formula above and it did not work. Any suggestions?

Ryan Kramer

@Julie Still ,

It can be difficult to debug it this way. I would take the first IF condition and then verify that works, and add them on sequentially.

Ryan

Paul Newcome

It's the column names. Having the parenthesis in the column name threw me off a little. My apologies.

Try this...

=IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row, TODAY() >= [Closed Date (35 days after End Date)]@row - 5), "Red", IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row - 10, TODAY() >= [Closed Date (35 days after End Date)]@row - 20), "Yellow", IF(AND(TODAY() <= [Closed Date (35 days after End Date)]@row - 21, TODAY() >= [Closed Date (35 days after End Date)]@row - 34), "Green")))

Julie Still

That worked @Ryan Kramer . Thank you bunches. I have a meeting in 15 minutes and was hoping to show this formula with the presentation.

Paul Newcome

Glad you were able to get it working. 👍️