Formula Assistance

I am attempting to do the following:

If status is Complete or N/A = Green
If start date is within 5 days or past due and status is not Complete or N/A = Red
If start date is more than 5 days and status is not Complete or N/A = Yellow

I have the following formula, but no luck. Any help is greatly appreciated.

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", IF(Status@row <> "Complete", Status@row <> "N/A", "Yellow", "Green")))

Find more posts tagged with

Formulas

Accepted answers

All comments

Carson Penticuff

If I am understanding this correctly, you can simplify the formula quite a bit.

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", "Yellow"))

CSmall1122

Thanks Carson, this almost gets me where I want to be. For the Yellow, I want it to indicate even on rows without a start date and status is not Complete or N/A. This is where I'm struggling. Any tips on this? @Carson Penticuff

Nick Korna

Hi @CSmall1122,

This should work for you:

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF(AND(OR([Start Date]@row > TODAY(5), ISBLANK([Start Date]@row)), OR(Status@row <> "Complete", Status@row <> "N/A")), "Yellow", "Red"))

Hope this helps; if you've any questions or problems with it then just post! 😊

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.