Actual % Complete vs Expected % Complete

Deedee

Hey! Please, does anyone know how I can calculate the "Expected % Complete?" I have tried numerous formulas but it is not giving me the outcome that is needed. Will I need to use the "Duration" or the "Start Date" or "End Date" in any particular order?

The Formula I use to find Expected % Completed - =IFERROR(ROUND((TODAY() - [Start Date]@row) / [End Date]@row - [Start Date]@row), 2), "")

Paul Newcome

Give this a try:

=MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0)

Paul Newcome

Happy to help. 👍️

markkrebs

How about this?

=IF(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row) > 1, 1, NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))

If you wan to exclude holidays, I have a separate sheet with holidays and I reference that in the networkdays formula.

Deedee

Hey! thank you for your help. Unfortunately, it is still not properly giving me the expected % complete of each task. It shows mostly "1" which does not translate to anything.

markkrebs

The logic said if my end date is < today then make the expected to be 1 or 100%. Id change the column to a %. I assumed that if you were past the End date the expected is that you'd be 100% complete.

Deedee

Thank you for the explanation. Let me be more detailed in what I am asking.

1) I need a formula that calculates the scheduling status by analyzing a work item's current Actual Percent Completed versus the defined Expected Percent Completed. I assume that "Duration" plays a part in calculating when a particular task is expected to be complete (automatic input) as opposed to the actual % completed (manual).

2) I tried changing the column to a % as you suggested but that it went above the 3-digit number. I even changed the "End date" to "2025" just to see the outcome but I do not believe that caused a major change.

Please, let me know if you need more clarity or perhaps I am doing something awfully wrong

Paul Newcome

Give this a try:

=MAX(MIN((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 1), 0)

Deedee

It Worked!! Thanks Paul 😊

Paul Newcome

Happy to help. 👍️