Schedule Completion Percentage

jbeaty
jbeaty ✭✭
edited 02/26/24 in Formulas and Functions

Hello all and thanks for your help in advance!

I am working on building project plan templates, dashboards, and reports for my PMO.

As EV is not a part of the request, I need to focus solely on schedule.

Here is what I am looking to do and need advice on how to formulate...

A task has an Actual Start Date of 02/01/24 and a duration of 20 days, it should be 90% complete by EOD 02/26/24 (02/19/24 is non-working holiday). So 100% / 20 Days = 5% completion per day.

How do I build a formula to show what completion percentage we should be based on current date?

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    =NETWORKDAYS([Actual Start Date]@row, TODAY()) / 20
    

    Do you want something like a countdown and adjust depening on the date today ( TODAY() )?

    ...

  • jbeaty
    jbeaty ✭✭
    edited 02/26/24

    yes, but needs to work for all tasks and different duration. This task happens to be 20 days, but need formula to work for all tasks, regardless of duration. So cant hard code "20". Will need to be based on ([Actual Start Date]@row - [Actual End Date]@row), or just use the duration figure

    Thanks so much for stepping in!

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @jbeaty


    You can try this formula. The formula will check the Actual End Date being greater than today, i.e., in the future AND the percentage is not in the negative to show you the % complete.


    =IF(AND([Actual End Date]@row > TODAY(), NETWORKDAY([Actual Start Date]@row, TODAY()) / Duration@row > 0), NETWORKDAY([Actual Start Date]@row, TODAY()) / Duration@row, IF([Actual End Date]@row < TODAY(), NETWORKDAY([Actual Start Date]@row, [Actual End Date]@row) / Duration@row, 0))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!