Calculating percentage complete

  • Thread starter Thread starter Geminisol
  • Start date Start date
G

Geminisol

I am trying to create a formula that will calculate the % complete based upon
a given start date and today's date. Can anyone recommend a formula?
 
I think we will need more information. Do you have an estimated complete
date or, alternatively, an estimated total number of days for completion of
the work? Are weekdays and/or holidays to be factored in (that is, not
counted as days worked)?

Rick
 
Thanks so much for your reply!

The start date for the phase was 3/8/08 and the Complete date was 3/25/08
for a duration of 17 days. Start of next phase date is 3/26/08. I'd like to
be able to calcuate the % complete of each phase, as well as the % complete
across the project based upon elapsed time between phase and/or project start
date through the present time of anyone looking at the spreadsheet. Meaning,
anytime the spreadsheet is opened, the % complete would calculate based upon
the present time. Please let me know if you require any additional
information and thanks so much for your help!

Weekdays and holidays can be factored in.
 
Weekdays and holidays can be factored in.

The above statement is a little ambiguous... factored in how? Count them?
Don't count them? For example, if a project started on 4/1/2008 which is a
Tuesday and its duration is 14 days, is the completion day 4/15/2008 or
4/21/2008?

Also, if you told us which row or column the Start Date's and Duration's
were in, that would help. Also, do you have a row or column already devoted
to the totals for each phase and for the whole job? If so, what are they?

Rick
 
Weekends and holidays should be counted.

Project Start Date - 3/8/2008
cell J5

Estimated Project End Date - 12/31/2009
cell K5

Estimated Project Duration - 653 Days
cell L5

1st Phase Start Date - 3/8/08 (references cell J5,start date of the project)
1st Phase Completion Date - 3/25/2008
cell Q5

Actual Duration - 17 days
cell R5

Next Phase Start Date - 3/26/08

Duration to Date in Phase - ? (don't have row/column devoted to this value
yet)

% Complete in Phase - ? (don't have row/column devoted to this value yet)

Total % Complete of Project - ? (Project has a total of 5 Phases. I don't
have row/column devoted to this value yet).
 
Okay, I think the following will do what you want. You were still a little
skimpy on the details to be able to give you concrete formula, so I'll make
some assumptions (you will have to adjust them for your actual conditions if
my guesses are wrong).

First off, the Estimated Project Duration for the dates you gave is 663, not
653. You should be getting that number by placing the formula =K5-J5 in cell
L5. You didn't say which column the "1st Phase Start Date" is in, so I'll
assume Column P (which means the date 3/8/08 is assumed to be in P5. Also,
since you don't yet have columns for "Duration to Date in Phase" and "%
Complete in Phase", I'll assume them to be Column S and Column T
respectively. Okay, with those assumptions in mind, put the following
formulas in the indicated cells...

S5: =TODAY()-P5 <<Format as General or Number>>

T5: =S5/(Q5-P5) <<Format as Percentage>>

Some place, you will need a cell containing the total of all the "Duration
to Date" cells for all of the phases making up your job. Let's assume that
cell is in the "Duration to Date in Phase" column, at cell S99. Put this
formula in the next cell over (which is assumed to hold the "% Complete for
Total Job"

T99: =S99/L5

Now, none of these account for missed estimated phase completion dates as I
wasn't sure how you would want to handle them. For example, if you neglect
to adjust a phase date once it is missed, the value in T5 will show as being
over 100% (indicating you missed the date). If, however, you continually
update your estimated completion dates, T5 should remain under or at 100%.

It's a lot easier to account for the things you are looking for once you
lock down all the columns; but, since you haven't yet, hopefully the above
will get you started.

Rick
 
Back
Top