Status Update Formula

MiteshD

Dear Friends

I am new to Smartsheet and like to learn quickly from your experience. I have tried to update the status column based on the % completed byt this simple logic

0%--. Not started,

Greater then 0 % but < 100% - WIP

100% - completed

How I can make this more meaningful by connecting with duration or end date ?

For example--

status = Not started- Delayed (ORANGE COLOR) - if % complete = "0" and today's date is greater than start date-

status = Overdue- Delayed (RED COLOR) - if % complete < "100" and today's date is greater than end date-

status = WIP - AT RISK (YELLOW COLOR) - if % complete is Greater then 0 % but < 50% and Time elapsed 50% of duration

I am not able to figure this out. Will you please help ?

Thanks

Mitesh Desai

Christian Graf

This is how to use nested if statements.

The formula for the status column is:

=IF(AND([Percent Complete]@row = 0, [Percent Duration]@row > 0), "Not Started - Delayed", IF(AND([Percent Complete]@row > 0, [Percent Complete]@row < 1, [Percent Complete]@row > [Percent Duration]@row), "In Progress - On Track", IF(AND([Percent Complete]@row = 0, [Percent Duration]@row > 0), "Delayed - Overdue", IF(AND([Percent Complete]@row > 0, [Percent Complete]@row < 1, [Percent Duration]@row > 1), "In Progress - Overdue", IF(AND([Percent Duration]@row > 1, [Percent Complete]@row > 0), "In Progress - Overdue", IF(AND([Percent Duration]@row > 1, [Percent Complete]@row = 0), "Not Started - Overdue", ""))))))

The percent duration column formula is:

=(TODAY() - [Start Date]@row) / ([Estimated End Date]@row - [Start Date]@row)

Hopefully this helps and you can reverse engineer a few things.