% Complete (plan) formula based on baseline dates

Hello,

I have my actual % complete based on start and end dates. Now I'm trying to create a % Complete (plan) column based on status date and baselines dates like in MS Project.

In MS Project you dot he following

If([Baseline Estimated Finish]<=[Status Date],1,IIf([Baseline Estimated Start]>[Status Date],0,ProjDateDiff([Baseline Estimated Start],[Status Date])/ProjDateDiff([Baseline Estimated Start],[Baseline Estimated Finish])))

How can I achieve this in smartsheet?

Thank you.


Tags:

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @jenniferrdz !

    Your exact formula will depend on a few factors such as if you want weekends and holidays to count as working days (i.e., factored into the number of days to complete a task or not) and if you want the current day to be the point a task is planned to be completed versus the last day to work (i.e., a task due on the current day is or is not shown as 100% complete).

    Note that Smartsheet project settings don't count non working days in the build in duration, they essentially use the NETWORKDAY() function.

    I prepared the following example with a formula in % Complete (Planned) that computes:

    The number of days past the Baseline Start) / (The duration between Baseline Start and Baseline Finish in non-working days).


    Note that the formula returns 0% for tasks that start in the future and 100% for tasks that should be complete.

    There are a few other columns for context as well.

    The formula is:

    =IF(TODAY() < [Baseline Start]@row, 0, IF(TODAY() > [Baseline Finish]@row, 1, NETWORKDAY([Baseline Start]@row, TODAY()) / NETWORKDAY([Baseline Start]@row, [Baseline Finish]@row)))

    Hope that helps!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!