How to calculate the expected % complete at a particular date?
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

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

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.

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

Glad that resolved it. Thanks for letting know.
Help Article Resources
Categories
Check out the Formula Handbook template!