#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# WBS Overdue status trigger points

Options
✭✭✭✭✭
edited 12/09/19

Hi, I would like to look at setting up a formula that shows if a WBS status is either overdue (symbol would be ! High) or has say 5 days to go on the WBS and is only sitting at 60% completed (symbol would be - Medium).

The trigger point would have to be driven off the :Finish" date in the setup screen shot.

The colums would most likely be hidden, but would be used to trigger reports on the dashboard.

I hope the above makes sense :-)

Regards

Cathy

• Options

This is what you asked for:

=IF(AND(Finish1 < TODAY(), Complete1 < 1), "", IF(AND(Finish1 - TODAY() <= 5, Complete1 < 0.6), "Medium", ""))

This has an additional set of logic where if it's supposed to be done within two days, and it's not, the status is set to High. You can continue to do further nesting, like this, to achieve more granular reporting. (Or as granular as you can, with Low Medium High)

=IF(AND(Finish1 < TODAY(), Complete1 < 1), "", IF(AND(Finish1 - TODAY() <= 5, Complete1 < 0.6), "Medium", IF(AND(Finish1 - TODAY() <= 2, Complete1 < 1), "High", "")))

• ✭✭✭✭✭
Options

Thanks Matt, I will test the formula to see how it goes.

Regards

Cathy

• ✭✭✭✭✭
Options

Hi Matt,

I tried the advised formula, but it does not seem to work. I am assuming the following

Finish1 = my "Finish" coloum

Complete1 = my "% Completed" coloum

Regards

Cathy

• ✭✭✭✭✭✭
Options

Cathy,

Can you be more specific? "it does not seem to work" is vague.

Did you get an error message? Which one?

A column named "% Completed" needs to be surrounded by square brackets like this [% Completed].

A cell in row 1 would be referenced like so:

[% Completed]1

I hope that helps.

Craig

• ✭✭✭✭✭
edited 08/28/17
Options

Hi Craig,

It is now working, the formula used is

=IF(AND(Finish14 < TODAY(), [% Complete]14 < 0.1), "", IF(AND(Finish14 - TODAY() <= 5, [% Complete]14 < 0.6), "High", ""))

• ✭✭✭✭✭✭
Options

Your solution seems odd to me, not least of which that an overdue task that is not complete will be blank, not "High".

Craig

This discussion has been closed.