% Scheduled versus % Complete

[Co9ug9ar]

I created a few taskes that have a start/finish/duration along with a
baseline start/finish/duration columns. I also have a % Complete field and a
% scheduled field. Basically what I am trying to do is show the executives
the % each task is complete vs. the % is should be complete (based on the
original schedule).

Is there a way in Project to calculate the %scheduled automatically like the
%complete column. Right now I am using the below caluculation which is very
close for individual tasks. However, when I have a task with several
subtasks, the % scheduled for the super-task is not right. I'm guessing its
because of some weighting issues on the duration of the subtasks, but I'm not
entirely sure.
If current date is < baseline start, then 0%
If current date is > baseline finish, then 100%
Otherwise...
(Duration between Current Date and Baseline Start Date)/Baseline Duration

Any suggestions on how I could sort this all out would be greatly
appreciated...

 

[John]

I created a few taskes that have a start/finish/duration along with a
baseline start/finish/duration columns. I also have a % Complete field and a
% scheduled field. Basically what I am trying to do is show the executives
the % each task is complete vs. the % is should be complete (based on the
original schedule).

Is there a way in Project to calculate the %scheduled automatically like the
%complete column. Right now I am using the below caluculation which is very
close for individual tasks. However, when I have a task with several
subtasks, the % scheduled for the super-task is not right. I'm guessing its
because of some weighting issues on the duration of the subtasks, but I'm not
entirely sure.
If current date is < baseline start, then 0%
If current date is > baseline finish, then 100%
Otherwise...
(Duration between Current Date and Baseline Start Date)/Baseline Duration

Any suggestions on how I could sort this all out would be greatly
appreciated...

Co9ug9ar,
Since you already have a baseline set, you could simply make use of
classical earned value metrics. In your case Schedule Variance (SV) or
Schedule Performance Index (SPI) might be just what you are looking for.
I'm not a big fan of the classical definition for SV or SPI (they are
financially based) but then, they have been used for years in Project
Management. For more information on classical earned value, take a look
at the Project help file or insert those fields as columns in your view
table and click on the "help on.." when the mouse is hovered over the
column header.

If you still want to "go your own way", using a custom formula for
individual tasks will work fine. However, for summary lines the same
formula (even though applied to the summary) will generally not be
valid. That is because Project uses a weighted formula for calculating
summary line progress (i.e. Actual Duration, % Complete, etc.) and
therefore you would also need a custom formula for summary lines.
Unfortunately one of the limitations with using formulas is that a
single formula will apply to all task rows, including summary rows, if
that option is selected.

It is possible to have customized formulas apply to task rows and
summary rows independently but it will require the use of an advanced
feature of Project, namely VBA. The code would be simple enough but as
with all macros, (except Event based macros), values will not calculate
automatically as they will with a formula. A macro must be initiated by
the user.

The bottom line is, take a look at the classical methods, they may well
be just what you need. If you do want to go with VBA, let us know, we
can help.

John
Project MVP

 

[Catfish Hunter]

I export the resource useage view to excel and set that up to do all my
baseline forecasting by resource and fill in the actual % complete. From
there I have an excel chart fot the executives. I do this for each resource
and an overall chart.

 

[Co9ug9ar]

I tried those fields and they weren't exactly what I was looking for. I
wouldn't mind going with the VBA, but is there a way to find out the
weighting that the %complete uses to build my own formulas from it?

 

[John]

I tried those fields and they weren't exactly what I was looking for. I
wouldn't mind going with the VBA, but is there a way to find out the
weighting that the %complete uses to build my own formulas from it?

Co9ug9ar,
Sure, Project's "formula" is no mystery but it does generate a fair
number of questions in the newsgroup (matter of fact we have a related
question yesterday). At a summary level Project calculates % Complete as:
(sum of subtask Actual Durations)/(sum of subtask Durations) * 100%

Hopefully that will get you started on your own formula.

John
Project MVP

 

[Co9ug9ar]

Sure, Project's "formula" is no mystery but it does generate a fair

number of questions in the newsgroup (matter of fact we have a related
question yesterday). At a summary level Project calculates % Complete as:
(sum of subtask Actual Durations)/(sum of subtask Durations) * 100%

Hopefully that will get you started on your own formula.

John
Project MVP

I haven't written a lot of VB codes, and its been a while since I did that
so as much help as you can give me is greatly appreciated... Is there a way
to see how many subtasks there are for each main task to create a loop to
traverse through the durations and if so how would I access that field from
the task.

Thanks,
Keith

 

[Jan De Messemaeker]

Hi Cougar,

No need for counting

How about

For each SmallJob in BigJob.outlinechildren
......
Next Smalljob

generates all the subtasks of a summary task

Hope this helps

 

[John]

Sure, Project's "formula" is no mystery but it does generate a fair
number of questions in the newsgroup (matter of fact we have a related
question yesterday). At a summary level Project calculates % Complete as:
(sum of subtask Actual Durations)/(sum of subtask Durations) * 100%

Hopefully that will get you started on your own formula.

John
Project MVP

I haven't written a lot of VB codes, and its been a while since I did that
so as much help as you can give me is greatly appreciated... Is there a way
to see how many subtasks there are for each main task to create a loop to
traverse through the durations and if so how would I access that field from
the task.

Thanks,
Keith[/QUOTE]

Keith,
Well you see it's like this. The code could be quite trivial or it could
be a little more.... interesting. If your file has just one indenture
(i.e. one summary line and everything else is a subtask) then the
OutlineChildren property of the summary line task object would work fine
for the loop. On the other hand if the file has multiple levels of
indenture the OutlineChildren and OutlineParent properties might be
useful but some scheme needs to be used to keep track of each indenture.
For example, let's say your file has two indenture levels as follows:

Summary 1
Summary 2
subtask a
subtask b
Summary 3
subtask c
subtask d

The values you want to calculate for summaries 2 and 3 are simply the
result of the formula I provided in my previous response. However, the
value for summary 1 is NOT the value of summaries 2 & 3 but rather the
result of using subtasks a through d in the formula. In other words, the
value at any summary level is calculated based only on the performance
tasks (tasks with actual effort performed by resources assigned).

Confused? I hope not. The bottom line is that the code is not trivial
but it is a nice challenging exercise for VBA. If you want more detailed
help, I'll ask that you contact me direct. Let me know, I'll give you my
direct contact info.

John
Project MVP

 

[John]

Hi Cougar,

No need for counting

How about

For each SmallJob in BigJob.outlinechildren
.....
Next Smalljob

generates all the subtasks of a summary task

Hope this helps

Jan,
You are correct but it may not be quite as simple as that. See my latest
response.

John

 

[Co9ug9ar]

I found one of the guys in my IT department who said he'd be able to set up
to the code for me. Thank you very much for all the help, I was really lost
for a while.

 

[John]

I found one of the guys in my IT department who said he'd be able to set up
to the code for me. Thank you very much for all the help, I was really lost
for a while.

Co9ug9ar,
Duh! Where was my brain?? If I hadn't been so quick in my initial
response I might have realized that what you want to do CAN be done with
formulas. It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1

A VBA solution will still work of course and it will be more efficient
but at least the above is an alternate approach and doesn't require VBA
code. As I mentioned earlier, a formula approach also has the advantage
of calculating automatically.

John
Project MVP

 

[John]

I found one of the guys in my IT department who said he'd be able to set up
to the code for me. Thank you very much for all the help, I was really
lost
for a while.

Co9ug9ar,
Duh! Where was my brain?? If I hadn't been so quick in my initial
response I might have realized that what you want to do CAN be done with
formulas. It will just take 3 custom fields instead of one. Here's the
basic outline of the process.
1. Create three custom fields (e.g. Text1, Duration1, and Duration2)
2. At the task level use the following formulas:

Text1=IIf([Duration1]/[Duration2]>=1,"100%",IIf([Duration1]/[Duration2]=0
,0,format([Duration1]/[Duration2],"#.00%")))

Duration1=IIf(projdatediff([Baseline Start],[Date1])>[Baseline
Duration],[Baseline Duration],IIf(projdatediff([Baseline
Start],[Date1])<=0,0,projdatediff([Baseline Start],[Date1])))

Duration2=[Baseline Duration]

3. At the summary level use "rollup - sum" for Duration1 & Duration2
4. At the summary level "use formula" for Text1

A VBA solution will still work of course and it will be more efficient
but at least the above is an alternate approach and doesn't require VBA
code. As I mentioned earlier, a formula approach also has the advantage
of calculating automatically.

John
Project MVP

People,
There is a slight "error" in my formula for Duration1. While testing it
I used Date1 in lieu of the Current Date. I forgot to make the
adjustment when I copied/pasted the test formula into my response above.
Therefore, the Current Date should be substituted for Date1.

Sorry for the misinformation.
John
Project MVP

 

[% complete]

John-I am new to Project-i am working with a teammates project plan. The $
complete does not tally up to the baseline--why would that b3?

At a summary level Project calculates % Complete as:
(sum of subtask Actual Durations)/(sum of subtask Durations) * 100%

 

[John]

John-I am new to Project-i am working with a teammates project plan. The $
complete does not tally up to the baseline--why would that b3?

At a summary level Project calculates % Complete as:
(sum of subtask Actual Durations)/(sum of subtask Durations) * 100%

% complete,
First of all, I think there is either a problem with your keyboard or
you are not proofreading your post before you hit send. For example, in
your second sentence did you mean to say % complete or are you in fact
referring to the total cost (i.e. $ complete) at a summary level?

Baseline values in Project are always static. That is, they represent a
snapshot of a particular field at the point in time when the baseline
was set (or reset). So given that, what exactly are you expecting to
"tally up"?

I know, if you are a first time user, Project can be very confusing, but
that's why we are here to help.

John
Project MVP