Tracking/Comparing Monthly Progress

[Kristi F]

One of the PMs would like to see a report indicating last month's reporting
%Complete against this month's reporting %Complete, and have this information
reflected in a Gantt Chart. What is the best way to accomplish this?

 

[John]

One of the PMs would like to see a report indicating last month's reporting
%Complete against this month's reporting %Complete, and have this information
reflected in a Gantt Chart. What is the best way to accomplish this?

Kristi,
I don't know if this is necessarily the "best way" but it should do the
trick. Set up two spare text fields, one to hold last's months %
Complete values (copied to the spare field each month) and the other to
hold a formula for calculating the "Complete through" date for last
month. That date will be the task Start + % Complete x task Duration.
This could either be done with a formula or with a VBA macro. To use a
formula try the following (assume the Text2 field holds last month's %
Complete values):
Text1=DateAdd("d",[Text2]x[Duration],[Start])
Once the formula is in place, go to Format/Bar Styles and set up a bar
to go from Actual Start to Text1. Then save and/or print the view.

It is possible that the Compare Project Versions utility could be used
to show the difference in % Complete values from one month to the next
but it might be a little more complicated to set up.

Hope this helps.
John
Project MVP

 

[Kristi F]

Hi John,

Thank you! However, I can't seem to get my calculated Complete Through
column to be correct. I tried the Compare Project Versions utility, which
seemed to work, but I can't seem to get it to map the progress lines on the
Gantt Chart. Do you know if there is a way to do this?

Thanks!
Kristi

One of the PMs would like to see a report indicating last month's reporting
%Complete against this month's reporting %Complete, and have this information
reflected in a Gantt Chart. What is the best way to accomplish this?

Kristi,
I don't know if this is necessarily the "best way" but it should do the
trick. Set up two spare text fields, one to hold last's months %
Complete values (copied to the spare field each month) and the other to
hold a formula for calculating the "Complete through" date for last
month. That date will be the task Start + % Complete x task Duration.
This could either be done with a formula or with a VBA macro. To use a
formula try the following (assume the Text2 field holds last month's %
Complete values):
Text1=DateAdd("d",[Text2]x[Duration],[Start])
Once the formula is in place, go to Format/Bar Styles and set up a bar
to go from Actual Start to Text1. Then save and/or print the view.

It is possible that the Compare Project Versions utility could be used
to show the difference in % Complete values from one month to the next
but it might be a little more complicated to set up.

Hope this helps.
John
Project MVP

 

[John]

Hi John,

Thank you! However, I can't seem to get my calculated Complete Through
column to be correct. I tried the Compare Project Versions utility, which
seemed to work, but I can't seem to get it to map the progress lines on the
Gantt Chart. Do you know if there is a way to do this?

Thanks!
Kristi

"John" wrote:

Kristi,
My bad. Normally I test all formulas and macros that I publish in a
response but I was too rushed and/or lazy to test the formula I
provided. It will not work as written. First, I had forgotten that spare
text fields are not available when defining bar styles. Therefore,
although it is ok to use a spare text field to hold last month's %
Complete values, the calculated field must be in a spare date field
(e.g. Start10). Second, I forgot to mention that if the % Complete
values are copied and pasted into the spare field, they must be
converted to a decimal values in the formula. Third, I neglected to
mention that Project stores time related values (e.g. Duration) in
minutes so a compensation factor must be used in the formula to convert
to days. As you can see I screwed it up pretty good.

Now that it is a new day and I can take a little more time, here is the
correct formula: (Note that I use Start10 for the formula since other
spare date fields are often used for other purposes. Text2 is still used
for last month's % Complete values.)
Start10=DateAdd("d",mid([Text2],1,instr(1,[Text2],"%")-1)/100*[Duration]/
480,[Start])

With regard to using the Compare Project Versions utility, it can be
used but as I said, it is more complicated. The difference values for %
Complete between last month's file and this month's file are written
into a spare number field in the new file that is created as a result of
the comparison. As before a custom field with a formula must be created
in a spare date field in order to create a separate bar style. Not
exactly convenient or straightforward. All in all, it was a bad idea on
my part to even suggest using the utility.

OK, you see how badly I can mess up. I apologize for the inconvenience.
If you have further questions or problems please post again or you may
contact me direct.

Hope this helps.
John
Project MVP

 

[Kristi F]

Hi John,

That works much better thanks. Am I correct in assuming that the "closets"
way to get my duration value to calculate to the approximate working times
(assuming that we work 5 days out of 7) that I need to (480/7)*5? Or is
there a better way to do this as well?

Thanks,
Kristi

Hi John,

Thank you! However, I can't seem to get my calculated Complete Through
column to be correct. I tried the Compare Project Versions utility, which
seemed to work, but I can't seem to get it to map the progress lines on the
Gantt Chart. Do you know if there is a way to do this?

Thanks!
Kristi

"John" wrote:

Kristi,
My bad. Normally I test all formulas and macros that I publish in a
response but I was too rushed and/or lazy to test the formula I
provided. It will not work as written. First, I had forgotten that spare
text fields are not available when defining bar styles. Therefore,
although it is ok to use a spare text field to hold last month's %
Complete values, the calculated field must be in a spare date field
(e.g. Start10). Second, I forgot to mention that if the % Complete
values are copied and pasted into the spare field, they must be
converted to a decimal values in the formula. Third, I neglected to
mention that Project stores time related values (e.g. Duration) in
minutes so a compensation factor must be used in the formula to convert
to days. As you can see I screwed it up pretty good.

Now that it is a new day and I can take a little more time, here is the
correct formula: (Note that I use Start10 for the formula since other
spare date fields are often used for other purposes. Text2 is still used
for last month's % Complete values.)
Start10=DateAdd("d",mid([Text2],1,instr(1,[Text2],"%")-1)/100*[Duration]/
480,[Start])

With regard to using the Compare Project Versions utility, it can be
used but as I said, it is more complicated. The difference values for %
Complete between last month's file and this month's file are written
into a spare number field in the new file that is created as a result of
the comparison. As before a custom field with a formula must be created
in a spare date field in order to create a separate bar style. Not
exactly convenient or straightforward. All in all, it was a bad idea on
my part to even suggest using the utility.

OK, you see how badly I can mess up. I apologize for the inconvenience.
If you have further questions or problems please post again or you may
contact me direct.

Hope this helps.
John
Project MVP

 

[John]

Hi John,

That works much better thanks. Am I correct in assuming that the "closets"
way to get my duration value to calculate to the approximate working times
(assuming that we work 5 days out of 7) that I need to (480/7)*5? Or is
there a better way to do this as well?

Thanks,
Kristi

Kristi,
Ok, it' still "my bad". Although I indeed tested the formula last time,
it still isn't right for what you want to see. When I tested it I used %
complete values for last month were less then those of the current month
so the Gantt bar was shorter and everything looked fine. I neglected to
take into account the difference between Duration and the task span line
shown on the Gantt graphic. In other words the Duration field in Project
is given in working days but the display is in elapsed days because
non-working time (e.g. weekends) are included in the timescale display.
I should have use the function that adds working days instead of elapsed
days. The correct formula is:

Start10=projDateAdd([Start],mid([Text2],1,instr(1,[Text2],"%")-1)/100*[Du
ration])
In the case of this formula, Duration is in days (I have no idea why) so
the conversion factor (480 minutes/day) is not needed.

With regard to your follow-on question, I don't understand what you are
asking, but let me take a stab at an answer. Project normally works with
working time. Therefore Duration values are expressed in working hours,
days, months, etc. although elapsed time can be entered (e.g. "xxed" =
"xx" elapsed days). Project uses a normal default 5 day work week with 8
hour work days. Task Start and Finish dates are also calculated in terms
of working time based on the Project calendar. However, Project stores
time in its underlying database in minutes. When using time related data
in formulas or in VBA, conversion factors are often necessary to convert
the minutes to hours or days depending on what the formula is
calculating. Conversion of minutes to hours is straightforward (60
min/hr). Conversion of minutes to days or weeks is also straightforward
(480 min/day and 2400 min/wk), assuming the default 8 hour workday and 5
day workweek. Conversion to months becomes a little less straightforward
because the days in a month vary. If the default 20 day month
(Tools/Options/Calendar tab) is used then the conversion is 48000
min/mon). However when months are the desired dimension of the
calculation, many people don't expect the result they get (they think in
terms of calendar months) and become confused. That's one reason I
recommend Duration always be expressed in days.

Again, sorry for my shortcomings regarding the formula I posted.

John
At this moment I don't feel like an MVP

 

[Serge]

Thank you John,
It's very usefull formula, but I found that it's work until task does not
split.
Do you know fields that shows days without work?
Serge

Hi John,

That works much better thanks. Am I correct in assuming that the "closets"
way to get my duration value to calculate to the approximate working times
(assuming that we work 5 days out of 7) that I need to (480/7)*5? Or is
there a better way to do this as well?

Thanks,
Kristi

Kristi,
Ok, it' still "my bad". Although I indeed tested the formula last time,
it still isn't right for what you want to see. When I tested it I used %
complete values for last month were less then those of the current month
so the Gantt bar was shorter and everything looked fine. I neglected to
take into account the difference between Duration and the task span line
shown on the Gantt graphic. In other words the Duration field in Project
is given in working days but the display is in elapsed days because
non-working time (e.g. weekends) are included in the timescale display.
I should have use the function that adds working days instead of elapsed
days. The correct formula is:

Start10=projDateAdd([Start],mid([Text2],1,instr(1,[Text2],"%")-1)/100*[Du
ration])
In the case of this formula, Duration is in days (I have no idea why) so
the conversion factor (480 minutes/day) is not needed.

With regard to your follow-on question, I don't understand what you are
asking, but let me take a stab at an answer. Project normally works with
working time. Therefore Duration values are expressed in working hours,
days, months, etc. although elapsed time can be entered (e.g. "xxed" =
"xx" elapsed days). Project uses a normal default 5 day work week with 8
hour work days. Task Start and Finish dates are also calculated in terms
of working time based on the Project calendar. However, Project stores
time in its underlying database in minutes. When using time related data
in formulas or in VBA, conversion factors are often necessary to convert
the minutes to hours or days depending on what the formula is
calculating. Conversion of minutes to hours is straightforward (60
min/hr). Conversion of minutes to days or weeks is also straightforward
(480 min/day and 2400 min/wk), assuming the default 8 hour workday and 5
day workweek. Conversion to months becomes a little less straightforward
because the days in a month vary. If the default 20 day month
(Tools/Options/Calendar tab) is used then the conversion is 48000
min/mon). However when months are the desired dimension of the
calculation, many people don't expect the result they get (they think in
terms of calendar months) and become confused. That's one reason I
recommend Duration always be expressed in days.

Again, sorry for my shortcomings regarding the formula I posted.

John
At this moment I don't feel like an MVP

 

[John]

Thank you John,
It's very usefull formula, but I found that it's work until task does not
split.
Do you know fields that shows days without work?
Serge

Serge,
There are no Project fields that directly show task split start and stop
information. However, that information is available through VBA. I have
responded to several posts, including the VBA code to obtain that
information, previously in this newsgroup. Try doing a search on posts
that include the subject "split" with "John" as an author.

John
Project MVP

Hi John,

That works much better thanks. Am I correct in assuming that the
"closets"
way to get my duration value to calculate to the approximate working
times
(assuming that we work 5 days out of 7) that I need to (480/7)*5? Or is
there a better way to do this as well?

Thanks,
Kristi

Kristi,
Ok, it' still "my bad". Although I indeed tested the formula last time,
it still isn't right for what you want to see. When I tested it I used %
complete values for last month were less then those of the current month
so the Gantt bar was shorter and everything looked fine. I neglected to
take into account the difference between Duration and the task span line
shown on the Gantt graphic. In other words the Duration field in Project
is given in working days but the display is in elapsed days because
non-working time (e.g. weekends) are included in the timescale display.
I should have use the function that adds working days instead of elapsed
days. The correct formula is:

Start10=projDateAdd([Start],mid([Text2],1,instr(1,[Text2],"%")-1)/100*[Du
ration])
In the case of this formula, Duration is in days (I have no idea why) so
the conversion factor (480 minutes/day) is not needed.

With regard to your follow-on question, I don't understand what you are
asking, but let me take a stab at an answer. Project normally works with
working time. Therefore Duration values are expressed in working hours,
days, months, etc. although elapsed time can be entered (e.g. "xxed" =
"xx" elapsed days). Project uses a normal default 5 day work week with 8
hour work days. Task Start and Finish dates are also calculated in terms
of working time based on the Project calendar. However, Project stores
time in its underlying database in minutes. When using time related data
in formulas or in VBA, conversion factors are often necessary to convert
the minutes to hours or days depending on what the formula is
calculating. Conversion of minutes to hours is straightforward (60
min/hr). Conversion of minutes to days or weeks is also straightforward
(480 min/day and 2400 min/wk), assuming the default 8 hour workday and 5
day workweek. Conversion to months becomes a little less straightforward
because the days in a month vary. If the default 20 day month
(Tools/Options/Calendar tab) is used then the conversion is 48000
min/mon). However when months are the desired dimension of the
calculation, many people don't expect the result they get (they think in
terms of calendar months) and become confused. That's one reason I
recommend Duration always be expressed in days.

Again, sorry for my shortcomings regarding the formula I posted.

John
At this moment I don't feel like an MVP