How to calculate the expected % complete at a particular date?

Options

Afternoon,

I am trying to illustrate some expected % vs actual % completed, using two dates.

So far I have input the below which based off today shows the task should be 42% complete.

=IF([Start Date]@row <= TODAY(), NETWORKDAYS([Start Date]@row, MIN(TODAY(), [End Date]@row)) / [Duration Between Start & End Date]@row, 0)

When I build out this formula to see the expected % complete as at 30 June, I would expect this to be 100% as in line with the end date being 30/06/24. However it comes out at 71.8%.


Can you point out where I am going wrong please?

Thanks in advance

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @JamesQ4

    It looks like your formula in Duration Between Start and End Date is not NETWORKDAYS.

    My thinking is....

    6/30/24 minus 1/1/24 is 181 days. I think this is what you are doing.

    1/1/24 to 6/30/24 assuming 5 day weeks and no holidays is 130 net work days. And it looks like these assumptions are valid.

    Because 130/181= 71.8% which is suspiciously similar to what you are seeing.

    So I think you are including weekends in the denominator but not the numerator. Try changing the formula in Duration Between Start and End Date to use NETWORKDAYS. This will also increase the % complete for TODAY() as well.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @JamesQ4

    It looks like your formula in Duration Between Start and End Date is not NETWORKDAYS.

    My thinking is....

    6/30/24 minus 1/1/24 is 181 days. I think this is what you are doing.

    1/1/24 to 6/30/24 assuming 5 day weeks and no holidays is 130 net work days. And it looks like these assumptions are valid.

    Because 130/181= 71.8% which is suspiciously similar to what you are seeing.

    So I think you are including weekends in the denominator but not the numerator. Try changing the formula in Duration Between Start and End Date to use NETWORKDAYS. This will also increase the % complete for TODAY() as well.

  • JamesQ4
    JamesQ4 ✭✭✭
    Options

    Thank you @KPH, this has helped resolve the issue, really appreciate it!!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Glad that resolved it. Thanks for letting know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!