IF 30 days past Planned end date show red

Melissa Torrez

I am trying to create a formula that looks at Planned End Date and determines the following:

If <= Today then Green

IF 1 day past planned end date then Yellow

IF 30 days past end date then Red

=IF([PLANNED END DATE]@row(30), "Red", IF([PLANNED END DATE]@row(1), "Yellow", IF([PLANNED END DATE]@row=TODAY)

Can I do this without having to create a helper column?

Mike TV

@Melissa Torrez

=IF([Planned End Date]@row<=TODAY(-30), "Red", IF([Planned End Date]@row=TODAY(-1), "Yellow", IF([Planned End Date]@row<=TODAY(), "Green"

The above is the way you'd do it. However, you're going to have some problems I think with this exact formula. Your criteria I don't think are planned out properly. For example, this formula will mark things 30 days or more passed the Planned End Date as Red. Things will only be in Yellow if it's only 1 day passed the Planned End Date. When it's 2 days passed it'll be Green I think. You'd want to change the = to <= for the yellow logic. Then for the green logic instead of <= I believe you'd want to change that to greater than TODAY() or else I don't think Green would ever show up.

Melissa Torrez

@Mike TV do you put the <= outside the parenthesis for example the below

=IF([PLANNED END DATE]@row >= (30), "Red", IF([PLANNED END DATE]@row <= (1), "Yellow", IF([PLANNED END DATE]@row > TODAY())))

Mike TV

@Melissa Torrez

They go outside the parenthesis but you're missing the TODAY function.

=IF([PLANNED END DATE]@row >= TODAY(30), "Red", IF([PLANNED END DATE]@row <= TODAY(-1), "Yellow", IF([PLANNED END DATE]@row > TODAY())))