Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Start Dates Are Earlier than Target Dates

We have a project that was pushed up on the timeline so the target dates are later than the actual dates. This is causing a negative Schedule Delta & a negative Schedule Delta (working days) due to the formulas. Is there a change I should make to the formula to reflect that this work is actually on time since it's before the targeted dates? This is causing our schedule health formula to turn red when in reality it should be showing green.


Schedule Health Formula: =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

Schedule Delta (%) Formula: =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

Schedule Delta (Working Days) Formula: =IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")


Thank you!


Tags:

Best Answer

  • Employee
    Answer ✓

    Hi @Sarah123

    The status is Red because the cell in the [Schedule Delta (%)] for that row is blank (which is less than 10%).

    If you'd like this to be a blank cell instead of having a status indicator, you can add another IF statement to your formula:

    =IF([Schedule Delta (%)]@row = "", "", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))

    You can of course have this be a different colour... such as Green, if that's what you'd prefer for blank cells:

    =IF([Schedule Delta (%)]@row = "", "Green", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))


    Does that make sense?

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Answers

  • Hi @Sarah123

    Your first formula returns "Red" when the Schedule Delta (%) is greater than 10%. This means that even if you're at 90%, it will show Red. I believe you may mean less than 10%? If so, simply swap around the > to be < and you should be good to go!

    =IF([Schedule Delta (%)]@row < 0.1, "Red",

    Full Schedule Health Formula:

    =IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))


    Then for your negative percents, we'll need to adjust your third formula (which will update your second one automatically).

    Right now you're looking for the working days between the Target End Date and the End Date. However you're setting the Target date as the "Start Date" when we know it's in the future from the actual End Date (meaning you'll always have a negative number).

    Instead, swap around the placement of these two references within the NETWORKDAYS.

    Original:

    IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1,

    Adjusted:

    IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1,


    You will now have a positive percent if the actual end date is earlier than the Target end date. I would actually suggest reversing this same structure for your previous IF statement as well (when the actual end date is past the target date). That will give you a negative number, therefore a negative percent, and a red status ball.


    Schedule Delta (Working Days) Formula:

    =IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1, ""))), "")


    Let me know if I can clarify anything further or if this works for you!

    Cheers,

    Genevieve

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • ✭✭✭✭

    I'm not sure if I'm missing something. I updated the formulas to what you recommended and now everything is showing red.


    Schedule Health: =IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))


    Schedule Delta (working days): =IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1, ""))), "")



  • Employee
    Answer ✓

    Hi @Sarah123

    The status is Red because the cell in the [Schedule Delta (%)] for that row is blank (which is less than 10%).

    If you'd like this to be a blank cell instead of having a status indicator, you can add another IF statement to your formula:

    =IF([Schedule Delta (%)]@row = "", "", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))

    You can of course have this be a different colour... such as Green, if that's what you'd prefer for blank cells:

    =IF([Schedule Delta (%)]@row = "", "Green", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))


    Does that make sense?

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • ✭✭✭✭

    Yes, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6