WBS Overdue status trigger points

Cathy Fraser

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

 

 

MattH

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

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

 

Regards

Cathy

Cathy Fraser

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

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

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

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

Craig