Automate Status Based on Due Date and Complete Date

I have a status column that uses the Red, Yellow, Green, and Blue symbols. I would like it to automatically update based on the dates in the Due Date and Compete Date columns. The parameters are as follows:

If there is a Complete Date = Green

If there is no Complete Date, AND Due Date is over 7 days away = Blue

If there is no Complete Date, AND Due Date is less than 7 days away = Yellow

If there is no Complete Date, AND Due Date is past today = Red

This is the formula I've tried, but I can't get it to work.

=IF(NOT(ISBLANK([Complete Date]@row)), "Green", IF(AND([Due Date]@row > TODAY(-7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(-7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row), “Red"))))

Any suggestions?

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey Amy

Let's try this

=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row >=TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row <=TODAY(7), [Due Date]@row>=TODAY(), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

Did this fix it?

Kelly

All comments

Kelly Moore

Hey @Amy V

Does this work for you?

=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

Kelly

Amy V

@Kelly Moore, thank you for your help!

That works better—I am not getting the “#UNPARSEABLE” error now. I discovered that when the Due Date was exactly 7 days away, no color was returned, so I adjusted the date for the yellow calculation to add 8 instead of 7. This is what I have currently.

=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(8), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))

The last IF statement (Red) is the only thing not working now. No matter how old of a date I put in the Due Date column, the status stays yellow (when the Complete Date is blank). I’m not sure why it won’t change red.

Thank you so much for your help and being willing to share your time and expertise!

Kelly Moore

Hey Amy

Let's try this

Did this fix it?

Kelly

Amy V

@Kelly Moore,

This works great! I've run a variety of scenarios and they all work as they should. Thank you so much for your help!

Amy

Kelly Moore

Anytime. Glad it works

Kelly

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.