VBA access to Work and Actual Work by task per period?

[G Lykos]

Greetings! Am interested in calculating Work To Date and Actual Work To
Date per task. To do so would seem to involve looping through the tasks and
summing work and actual from beginning of project to the Status date.

What is the Project VBA access mechanism to do so? Is there an easier way
to do this? Exporting to Excel to mash it is a less-desirable approach -
would prefer to do this inside of Project and save results in custom fields,
hopefully such that automatic rollups to summary tasks are available.

Thanks,
George

 

[G Lykos]

Realize after poking around a little that method TimeScaleData is the
vehicle to access this information. Anyone aware of an existing program to
extract Work To Date and Actual Work To Date to custom fields? Or can the
calculational ability of a custom field be used to gather this data?

Thanks again,
George

 

[John]

Realize after poking around a little that method TimeScaleData is the
vehicle to access this information. Anyone aware of an existing program to
extract Work To Date and Actual Work To Date to custom fields? Or can the
calculational ability of a custom field be used to gather this data?

Thanks again,
George

George,
Calculational?? That's a new one.

A custom field is a spare field that has been customized with a value
list or a formula. If you are simply trying to store your "work to date"
and "actual work to date" values in a couple of spare fields, just do it
after the sum is calculated in the loop for Timescale vales.

John
Project MVP

 

[Dean C]

The standard field "Actual Work" is the sum of actual work.
The standard task field BCWS is the sum of baseline costs to the status date.
If you are not using the financial aspects of Project, then you can set the
rate for every resource to $1 per hour (or 480 to eliminate some math).
Customize a duration field with the formula [BCWS]*480 (or at $480 per hour,
[BCWS]).

If you have entered the actual rates, then you will need to divide the BCWS
by the rate, which would require a macro.

To verify that you have entered the formulas correctly, open the view
TaskUsage. In the graphics area on the left side, is a Details column. Right
mouse click anywhere in the graphics data area and select Cumulative Work.
This will display the cumulative work to every date displayed. Verify that
the value in the column for the status date aligns with your calculated
values.

Wouldn't it be nice to be able to drag the values under Statusdate in the
graphical area over to the tabular area? It's aggrevating to have data
displayed that you cannot easy access.

 

[Dean C]

I just discovered that if you only display cumulative work and collapse to
display only tasks, you can copy the data from the graphical area and paste
it into the tabular area. No custom field calculations are required.

The standard field "Actual Work" is the sum of actual work.
The standard task field BCWS is the sum of baseline costs to the status date.
If you are not using the financial aspects of Project, then you can set the
rate for every resource to $1 per hour (or 480 to eliminate some math).
Customize a duration field with the formula [BCWS]*480 (or at $480 per hour,
[BCWS]).

If you have entered the actual rates, then you will need to divide the BCWS
by the rate, which would require a macro.

To verify that you have entered the formulas correctly, open the view
TaskUsage. In the graphics area on the left side, is a Details column. Right
mouse click anywhere in the graphics data area and select Cumulative Work.
This will display the cumulative work to every date displayed. Verify that
the value in the column for the status date aligns with your calculated
values.

Wouldn't it be nice to be able to drag the values under Statusdate in the
graphical area over to the tabular area? It's aggrevating to have data
displayed that you cannot easy access.

George,
Calculational?? That's a new one.

A custom field is a spare field that has been customized with a value
list or a formula. If you are simply trying to store your "work to date"
and "actual work to date" values in a couple of spare fields, just do it
after the sum is calculated in the loop for Timescale vales.

John
Project MVP

 

[Dean C]

George and I had an off-line discussion that demonstrated the need for
clarification.

George,
You missed the bottom line of my message. You can just copy and paste the
cumulative baseline work from the graphical (right) side of the screen and
paste it in the tabular (left) side.

Select ViewMoreViewsTaskUsage
Right mouse click anywhere on the graphical side
Select Cumulative Work
Right mouse click anywhere on the graphical side
De-select Work
Scroll to the date that you are interested in and select and copy the entire
column
On the tabular side, insert either a custom duration field
(duration1-duration10) or a number field, then paste.

The differences between a number field and a duration field are that in a
duration field, you normally do not need to divide by 480 (60 minutes X 8
hours per day) and the numbers have units of time appended to them. Duration
fields can be used to roll up max, min, sum, and average to summary tasks.
Duration1-duration3 are used by Project in conjunction with Start1/Finish1,
Start2/Finish2 and Start3/Finish3 for doing pert analysis. When used for pert
analysis, they are re-titled (not renamed) Optimistic Dur., Expected Dur.
and Pessimistic Dur. I use the remaining duration fields for analyzing
change, with Duration4-Duration10 fields renamed:
Prev 2 Wks Total Slack
Prev 2 Wks Free Slack
Prev Wk Total Slack
Prev Wk Free Slack
Prev Month Dur
Prev 2 Wks Dur
Prev Wk Dur

Dean, thanks for your suggestions. We are in fact using live resource
rates, so the solution becomes a bit more complex.

Speaking of duration fields - what is the purpose of this data-type field?

Thanks,
George

I just discovered that if you only display cumulative work and collapse to
display only tasks, you can copy the data from the graphical area and paste
it into the tabular area. No custom field calculations are required.

The standard field "Actual Work" is the sum of actual work.
The standard task field BCWS is the sum of baseline costs to the status date.
If you are not using the financial aspects of Project, then you can set the
rate for every resource to $1 per hour (or 480 to eliminate some math).
Customize a duration field with the formula [BCWS]*480 (or at $480 per hour,
[BCWS]).

If you have entered the actual rates, then you will need to divide the BCWS
by the rate, which would require a macro.

To verify that you have entered the formulas correctly, open the view
TaskUsage. In the graphics area on the left side, is a Details column. Right
mouse click anywhere in the graphics data area and select Cumulative Work.
This will display the cumulative work to every date displayed. Verify that
the value in the column for the status date aligns with your calculated
values.

Wouldn't it be nice to be able to drag the values under Statusdate in the
graphical area over to the tabular area? It's aggrevating to have data
displayed that you cannot easy access.

Realize after poking around a little that method TimeScaleData is the
vehicle to access this information. Anyone aware of an existing program to
extract Work To Date and Actual Work To Date to custom fields? Or can the
calculational ability of a custom field be used to gather this data?

Thanks again,
George

George,
Calculational?? That's a new one.

A custom field is a spare field that has been customized with a value
list or a formula. If you are simply trying to store your "work to date"
and "actual work to date" values in a couple of spare fields, just do it
after the sum is calculated in the loop for Timescale vales.

John
Project MVP


Greetings! Am interested in calculating Work To Date and Actual Work To
Date per task. To do so would seem to involve looping through the tasks
and
summing work and actual work from beginning of project to the Status date.

What is the Project VBA access mechanism to do so? Is there an easier way
to do this? Exporting to Excel to mash it is a less-desirable approach -
would prefer to do this inside of Project and save results in custom
fields,
hopefully such that automatic rollups to summary tasks are available.

Thanks,
George