Update Status Column based % Complete, Finish Date and Today

Jacquita

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

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?

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.