Status Column Formula Help

Nick Patil

Hi all, I'm hoping someone can help me with a formula for the 'Status' column in my smartsheet.

I’d like to have the 'Status' based off of the '% Complete' column and refer to these percentages:

Status // Calculation

Past Due // <100% Complete after 'End Date'

Not Started // 0% Complete

In Progress // 0%> <99% Complete

Complete // 100% Complete

I'm having the most trouble with the Past Due formula. Please let me know if I need to provide more information.

Thank you in advance for any help you can offer!

Heather Duff

Hi @Nick Patil

My apologies! I had my greater than/less than signs flipped. Try this:

=if(AND([% complete]@row<1, [end date]@row<TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))

Best,

Heather

Paul Newcome

Try something like this...

=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row < 1, "In Progress", IF([End Date]@row > TODAY(), "Completed after End Date", "Complete")))

Heather Duff

Hi Nick,

Try this:

=if(AND([% complete]@row<1, [end date]@row>TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))

It translates to:

If % complete is less than 100 and the end date is in the past, show Past Due. Otherwise, if % complete is 0, show Not Started. Otherwise, if % complete is less than 100, show In progress. Otherwise, if % complete is 100, show Complete. Otherwise, show Error.

I just threw Error in there as a check - but it shouldn't come up. Let me know if it works for you!

Best,

Heather

Nick Patil

Hi Paul and Heather,

Thank you both so much, that was incredibly fast!

I tried your formula Heather it works great, but I have a few tasks that have a start/end date in the future - 7/15/21 for example and its showing as 'Past Due'.

Is there a way to make it 'Not Started' if the Start or End Date is in the future?

Heather Duff

Hi @Nick Patil

My apologies! I had my greater than/less than signs flipped. Try this:

=if(AND([% complete]@row<1, [end date]@row<TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))

Best,

Heather

Nick Patil

Hi Heather, that's perfect - thank you both!

Paul Newcome

Happy to help. 👍️