Formula Help??

Wanda Jackson

I'm trying to set up a formula to calculate a Reminder status by days.  I have a deadline date that is in the past, but it does not turn my status column to a "Red" dot, it remains green and I do not know how to add to this formula to make that happen.

Here's the formula I have in place, and it works, but I need status to change to "Red" dot if my deadline date is past due. Can you help?

=IF([Sponsorship Status Complete?]@row <> 1, IF([(HIDE) Sponsorship Info Status Reminder]@row < 1, "Green", IF([(HIDE) Sponsorship Info Status Reminder]@row <= 30, "Red", IF([(HIDE) Sponsorship Info Status Reminder]@row > 30, "Yellow"))))

Wanda Jackson

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response as the "Accepted Answer" so that others can know that a solution has been found.

Elisha Lester

I'm not sure if this will help or not but we use a standard formula in the "Status" column for most of our sheets and seems to work well. Takes into account % complete and start/finish dates.

=IF(AND(ISDATE(Start@row), ISDATE(Finish@row)), IF([% Complete]@row = 1, "Gray", IF(Finish@row < TODAY(), "Red", IF(AND(Start@row > TODAY(), [% Complete]@row > 0), "Green", IF(Start@row > TODAY(), "Not Started", IF([% Complete]@row < 0.95 * [Elapsed Time]@row, "Yellow", IF([% Complete]@row >= 0.95 * [Elapsed Time]@row, "Green", "Error")))))), "TBD")

Paul Newcome

Formulas work left to right, so you should be able to add it to the beginning.

=IF([Sponsorship Status Complete?]@row <> 1, IF([Deadline Date]@row < TODAY(), "Red", IF([(HIDE) Sponsorship Info Status Reminder]@row < 1, "Green", IF([(HIDE) Sponsorship Info Status Reminder]@row <= 30, "Red", IF([(HIDE) Sponsorship Info Status Reminder]@row > 30, "Yellow")))))

Wanda Jackson

Good morning Paul,

Thank you for helping with this formula. It did work for me!! Hope you have a great rest of your day!!.

Wanda J.

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response as the "Accepted Answer" so that others can know that a solution has been found.