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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!