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
Cathy Fraser
Cathy Fraser ✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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

 

 

symbles.JPG

setup.JPG

Comments

  • MattH
    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", "")))

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    Options

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

     

    Regards

    Cathy

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    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

     

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

     

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    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", ""))

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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.