Formula Help??

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

Find more posts tagged with

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.