Update Status Column based % Complete, Finish Date and Today

Hi,

I am trying to work out the correct formula so my Status Column updates automatically based on % Complete, Finish and Today's date.

I am using this formula for the Progress Column:

=IF(Status@row = "At Risk", "Red", IF(Status@row = "Pending", "Yellow", IF(Status@row = "In Progress", "Green", IF(Status@row = "Complete", "Blue"))))

But I am unable to land on the correct formula for the Status Column.

Thank you

Find more posts tagged with

Accepted answers

Paul Newcome

Line 6 wouldn't be at risk because it is not less than 50% complete.

Line 8 SHOULD be at risk based on the formula. How exactly are you populating the dates?

All comments

AravindGP

Hi @Jacquita

Please specify the logic you are looking to implement to decide At Risk, Pending, In Progress, and Complete.

Jacquita

Hi Aravind,

Thanks for reaching out.

Basically I want the Status categories to be automated based on % Complete against Today's date and the Finish date.

I thought I had it with this formula below, but every entry comes back as Pending.

And if I put a future date in Start Date, I get INVALID OPERATION.

=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [Finish Date]@row < TODAY()), "Pending", IF(AND([% Complete]@row < 0.5, [Finish Date]@row < =TODAY(5)), "At Risk", IF(AND([% Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", ""))))

Paul Newcome

Try this:

=IF([% Complete]@row = 1, "Complete", IF([Finish Date]@row< TODAY(), "Pending", IF(AND([% Complete]@row< 0.5, [Finish Date]@row<= TODAY(5)), "At Risk", "In Progress")))

Jacquita

hi Paul,

Many thanks for your help, that has got me closer.

A few questions please re example attached:

Row 2 - should be At Risk?

Row 3 - should be At Risk?

Row 6 - shows In Progress, but is not started (i.e )%).

Thank you

Smartsheet Formula.png

Paul Newcome

Try this:

=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row< 0.5, [Finish Date]@row<= TODAY(5)), "At Risk", IF([Finish Date]@row< TODAY(), "Pending", "In Progress")))

Jacquita

Brilliant thanks Paul.

Jacquita

A couple of queries, using the formula above:

Line 6 should be At Risk

I think Line 8 should be At Risk also?

thank you

Paul Newcome

Line 6 wouldn't be at risk because it is not less than 50% complete.

Line 8 SHOULD be at risk based on the formula. How exactly are you populating the dates?

Jacquita

Got it Paul, an error my end. Appreciate your help and persistence.

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