Actual % Complete vs Expected % Complete

Hey! Please, does anyone know how I can calculate the "Expected % Complete?" I have tried numerous formulas but it is not giving me the outcome that is needed. Will I need to use the "Duration" or the "Start Date" or "End Date" in any particular order?

The Formula I use to find Expected % Completed - =IFERROR(ROUND((TODAY() - [Start Date]@row) / [End Date]@row - [Start Date]@row), 2), "")


Best Answers

Answers

  • markkrebs
    markkrebs ✭✭✭✭✭✭

    How about this?

    =IF(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row) > 1, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))

    If you wan to exclude holidays, I have a separate sheet with holidays and I reference that in the networkdays formula.

  • Deedee
    Deedee ✭✭

    Hey! thank you for your help. Unfortunately, it is still not properly giving me the expected % complete of each task. It shows mostly "1" which does not translate to anything.


  • markkrebs
    markkrebs ✭✭✭✭✭✭

    The logic said if my end date is < today then make the expected to be 1 or 100%. Id change the column to a %. I assumed that if you were past the End date the expected is that you'd be 100% complete.

  • Deedee
    Deedee ✭✭

    Thank you for the explanation. Let me be more detailed in what I am asking.

    1) I need a formula that calculates the scheduling status by analyzing a work item's current Actual Percent Completed versus the defined Expected Percent Completed. I assume that "Duration" plays a part in calculating when a particular task is expected to be complete (automatic input) as opposed to the actual % completed (manual).

    2) I tried changing the column to a % as you suggested but that it went above the 3-digit number. I even changed the "End date" to "2025" just to see the outcome but I do not believe that caused a major change.


    Please, let me know if you need more clarity or perhaps I am doing something awfully wrong


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Give this a try:

    =MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Deedee
    Deedee ✭✭

    It Worked!! Thanks Paul 😊

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!