Schedule Health formula not working,

Finops C'ship
Finops C'ship ✭✭
edited 05/22/21 in Formulas and Functions

Hi,

Can anyone help me with a formula i have three scenario

.1 & 2. IF start date and end date % complete activity exceed the daily % completion , Status should be In progress and color the schedule health to Green

3 % complete is less than the expected work to be completed within the time frame it should be Red and status should be In progress



Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Good morning,

    Try this for your schedule health:

    =IF((NETWORKDAY([Start date]@row, today())/NETWORKDAY([start date]@row, [end date]@row))<[% complete]@row, "Red", "Green")

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Hi @Mark Cronk

    I think something is not correct on the formula as you could see % complete is more than 50% and start date has not yet came so this should be green not red.



  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    What formula are you using in your %complete column that's marking a takes complete when it hasn't started?

    Try this to correct you status issue:

    =IF(AND(ISDATE([start date]@row), [start date]@row<today()), IF((NETWORKDAY([Start date]@row, today())/NETWORKDAY([start date]@row, [end date]@row))<[% complete]@row, "Red", "Green"))

    Getting closer?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!