Schedule Completion Percentage

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
-
=NETWORKDAYS([Actual Start Date]@row, TODAY()) / 20
Do you want something like a countdown and adjust depening on the date today (
TODAY()
)?...
-
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!
-
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 GP| Delivery Manager
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!