How to combine if statements utilizing information in multiple columns

Hello,

I currently have the following formula to automatically have a color coded dot assigned in a column depending on the due date.

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow")))

Can you help me add to this same formula an if statement that specifies:

If Status in the row is "Complete", mark at green. If it's not, complete, follow the rest of the rules above.

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

Forklift_Expert

The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

I tested this on one of my sheets and it worked

=IF(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))

For you it should look like this:

=IF(Status@row = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

All comments

Forklift_Expert

The easiest way to implement this would be to ignore the due date logic entirely. I added an IF at the beginning that makes it only run through the code you have if the status is not "Done"

I tested this on one of my sheets and it worked

=IF(Status@row = "Done", "Green", IF(ISBLANK(Deadline@row), "", IF(Deadline@row - TODAY() > 5, "", IF(Deadline@row <= TODAY(), "Red", "Yellow"))))

For you it should look like this:

=IF(Status@row = "Completed", "Green", IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row - TODAY() > 5, "", IF([Due Date]@row <= TODAY(), "Red", "Yellow"))))

nancyp

That is extremely helpful and worked!! Thank you so very much!!!

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.