Project Plan Template Formula Issue

Options

I have a question on a formula used in a project plan template currently being used.

Many times, the % complete (expected) is greater than 100%. The formula currently being used is Expected Effort Complete/Cumulative Effort.

The Cumulative Effort is the duration of the task, in days, and the expected effort complete formula is IF(TODAY() > [Planned Finish]@row, Duration@row, IF(TODAY() >= [Planned Start]@row, TODAY() - [Planned Start]@row, 0)))

I cannot figure out where the formula error is that is giving me a greater than 100% expected complete figure.

Thanks!

Answers

  • Genevieve P.
    Options

    Hi @Nathan Umbriac

    Your calculation of the % complete (expected) will be greater than 100% if the expected effort complete formula outputs a number greater than the duration of the task.

    I believe this is happening since your formula is simply subtracting Today from the Start Date without taking into consideration work days.

    For example, if your Start Date was on a Friday and Today was Monday, then TODAY() - [Planned Start]@row would equal 4 days, but the Duration would actually be 2 working days. Does that make sense?

    To adjust this, you'll want to use the NETWORKDAYS function in your formula, like so:


    =IF(TODAY() > [Planned Finish]@row, Duration@row, IF(TODAY() >= [Planned Start]@row, NETWORKDAYS([Planned Start]@row, TODAY()), 0))


    Let me know if this resolves your issue!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nathan Umbriac
    Nathan Umbriac ✭✭✭✭✭
    Options

    This is great! Thank you @Genevieve P. Your guidance is much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!