Schedule Completion Percentage

jbeaty

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?

heyjay

=NETWORKDAYS([Actual Start Date]@row, TODAY()) / 20

Do you want something like a countdown and adjust depening on the date today ( TODAY() )?

jbeaty

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!

AravindGP

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))