Need some help with a health formula for task risk level analysis

Hello, I am currently working on a formula for the health column in my smartsheet that is based on the the task status and the end date of that particular task. This is what I have so far but what I need is for when the "end date" cell is blank I would like for the health of that task to be blue. The reason is we do not plan to fill in all the dates at one point as are tasks schedule is pretty up in the air but we would like to run analysis on the tasks that are scheduled.

=IF(Progress6 = "Completed", "Green", IF(AND(Progress6 <> "Completed", TODAY() > [Preliminary End Date]6), "Red", IF(AND(Progress6 = "Not Started"), "Blue", IF(AND(Progress6 = "In Progress"), "Yellow"))))

Right now it is:

Red-If past due and labeled as in progress or not started.

Red-If no date is in the end date column... THIS IS WHAT I WANT TO CHANGE.

Yellow - If the task is in progress and not past the due date.

Green - If the task is completed.

Blue - If the task is not started and is not past the due date... I would like to keep this as well.

Find more posts tagged with

Workspaces

Sheets

Formulas

Workflows in Smartsheet

Cell linking

Accepted answers

SmartLew

Smartsheet registers blank cells as being in the past, hence right now any blank dates are turning red, because it falls into the same category as your second IF Statement.

To cancel this out, At the front of your formula I would add another statment; IF([Preliminary End Date]6="","Blue".

i.e if date is blank, then blue

This IF statement will take priority and a blank cell will drive the blue value, meaning it won't cause issues in the other IF statement.

All comments

SmartLew

Smartsheet registers blank cells as being in the past, hence right now any blank dates are turning red, because it falls into the same category as your second IF Statement.

To cancel this out, At the front of your formula I would add another statment; IF([Preliminary End Date]6="","Blue".

i.e if date is blank, then blue

This IF statement will take priority and a blank cell will drive the blue value, meaning it won't cause issues in the other IF statement.

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.