Can I add something to color code for blank cells?

Hello,

I am using the following formula to represent health color for individual rows:

=IF([Start Date]40 > TODAY(), "Blue", IF(AND([% Complete]40 < 1, [End Date]40 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))

This formula works well for what I need except that I use a template project sheet with phases and tasks built in. The problem arises from the health indicator being red when the start and end dates are blank. In dashboards, this makes it appear the project is failing if I dont have a future start and end date on every row. At the moment, I set the dates in the template way out into the future to achieve the blue color. The cascading issue then becomes the duration and expected end date of the entire project is massively incorrect until I get a real date on every row (which doesnt happen right away for new projects).

In a perfect world, I would like to build the project template with associated tasks in the template file, set all the start dates and end dates as blanks and have the health bubbles blue.

Thus my question: Can I add to the formula above to check for blank cells and color them blue without ruining the logic of the formula as it currently is?

Any feedback, including alternative ways to handle my issue without modifying the formula, are welcome. Also, before someone thinks I know what I am doing...I did not write the above formula. It came as part of the template pack from Smartsheet. Thanks!

Find more posts tagged with

Templates

Formulas

functionality

Product Development

Project Management

Accepted answers

All comments

L_123

=if(or(isblank([Start Date]@row),isblank([End Date]@row),[Start Date]40 > TODAY()), "Blue", IF(AND([% Complete]40 < 1, [End Date]40 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"

Give that a try and let me know if it works for you.

Adam Fink

Thanks L@123. The formula works to color the health blue with blank cells, but it blocks the other coloring rules like green when start day is today or less, and red when end date is less than today. The health stay blue in all circumstances with this formula.

L_123

Can you show a snip of your sheet? I think I am misunderstanding something about what you want it to do in that case. Please blot out any sensitive information before posting.

Adam Fink

Hey L@123,

I think I got it!

=IF(AND(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))))

I tested this with blank dates and it turned blue, completed % of 100 (regardless of dates) and it was green, end dates earlier than today and it was red - and they all worked! Your formula helped get me there - thanks 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.