Overall project status updater in cell as percentage

  • HI all I need a help I had attached a sheet with the dates with project tasks. In one cell I need the overall project status in percentage. Eg. I have 5 tasks to complete If my Proposed start date is 1-Feb-2018 and Completed is 1-Feb-2018 then my project is 20% completed out of 5 tasks. Like that If my 2nd task Start date is 1-March-2018 and If I complete by 28-Feb-Itself it should calculate the overall percentage. Please help

  • Use a helper column in column D and enter =IF(C6="","",0.2)
    Drag this down over the range and sum the values in the % completed field.

    I am only assuming here that all you need is a finish date to be entered which would indicated completed regarrdless of the date as there are no permutations mentioned for early finish or late finish
    Also, what about the % of progress of each line up to 100%, do you need to see that?

    I am sure the experts here can provide an array formula for the % field, but here is my offering to help.

    hope this helps

  • Hi Thankyou so much for the reply. But i need in some other way. If 1st Task to be started on 1-jan-2018 and say actually started in 3-Jan-2018 the task is completed but delayed with 2 days. I need like this for all the tasks. Here no worries of holidays etc. Just need a overall percentage bar to be displayed.

  • Hi

    I think I understand your issue now.
    But you have 1 planned start and end date for all tasks ! is this correct?

    Also, do you need to know if the task is started early and finishes early. Many variables here...

    Are you looking for something like MS Project shows?
    If so

    Take a look the link below, as these are great samples that you may be able to adapt.


    Hope this helps.

  • Hi thanks for the reply. Yes what you are guessing is absolutely correct. I have one planned start and end date however i can include this if this gives me results. Yes I also want to know if the task is started early and finishes early.
    I saw the Chandoo blog but its not helping me with this case. I just need a simple status updated based on dates.

  • Hi

    Not very clear what you are now wanting, but I am sure the sample sheets on the Chandoo link, does exactly what you need and more.

    Also, the variables in this post are changing with each entry. This make it impossible to try and help.
    Can you please update the post and sample sheet with all requirements detailed and expected outcome, then maybe I and others can help.


  • Hi,

    I am so sorry for the confusion. I will tell you clearly what i need. I have a project start and end date and the project happens in 5 phases. Let us assume the Project start day is 1/15/2018 and the duration is 1 day so the predicted end day and actual end day is 1/15/2018 so the percentage completion is 20%(because 5 tasks). Also 2nd phase starting day is 7/2/2018 and the duration is 5 days so the predicted end day is 12/2/2018 so the percentage completed now becomes 20% and the overall status is 40% completed. What if the 2nd phase completes at 15/2/2018?? Project completion is 40% but delayed with 3 days so it should automatically push the end date by 3 days. I need like this.

  • Hi Appreciate the update. Can you update the sample sheet to reflect these changes as they are easier to understand in a format that you want. Please include sample data with expected results.

    Still very unsure as to why you would have all tasks / phases starting in parallel. Rarely do projects wotk like this.
    I think you need to revist a structure here otherwise, reconciling a project status cannot be achieved.

    What happens if you start late but finish early "SS FF" how are the tasks linked if one task delays and pushes out the project?
    Also, the predicted end date is not always actual end date, you should have an entry for both.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!