RGY Balls Question on formula to have them change upon reaching a date

Novice

I have a SS where I have contract dates. I've a column that is the 'Take Action Date' which is 300 days prior to the contract expiration date. When it hits the Take Action Date, I want the status ball to change to yellow. And when it reaches 120 days prior to the contract expiration date, I want the status ball to turn red. I cannot figure this out so any help you can provide will be appreciated!

KPH

Hi @Novice

It sounds like you want the status color to change based on the proximity of today's date to the contract expiration date and take action date (which is based on the contract expiration).

If so, you can use an IF formula in the column where you want the status ball to appear.

As well as the IF function, you will need to use the TODAY function (to return today's date).

By combining these functions with the date in your contract expiration date column, you can return red, yellow, or green, which will change the color of the ball.

The logic is

If Contract Expiration Date is before or on Today plus 120 days, then return red.

If that isn't true

If Contract Expiration Date is before or on Today plus 30 days, then return yellow.

If that isn't true

return green.

The formula looks like this:

=IF([Contract Expiration Date]@row <= TODAY(120), "Red", IF([Contract Expiration Date]@row <= TODAY(300), "Yellow", "Green"))

And that creates this:

KPH

Hi @Novice

It sounds like you want the status color to change based on the proximity of today's date to the contract expiration date and take action date (which is based on the contract expiration).

If so, you can use an IF formula in the column where you want the status ball to appear.

As well as the IF function, you will need to use the TODAY function (to return today's date).

By combining these functions with the date in your contract expiration date column, you can return red, yellow, or green, which will change the color of the ball.

The logic is

If Contract Expiration Date is before or on Today plus 120 days, then return red.

If that isn't true

If Contract Expiration Date is before or on Today plus 30 days, then return yellow.

If that isn't true

return green.

The formula looks like this:

=IF([Contract Expiration Date]@row <= TODAY(120), "Red", IF([Contract Expiration Date]@row <= TODAY(300), "Yellow", "Green"))

And that creates this:

Novice

Thank you so much - it worked perfectly!

KPH

Excellent. Thanks for letting me know!