Formulas with Time-Phased Data

[Steve K]

I want to write a formula to show project costs for only the current year.
Can anybody help me out?

I'm writing this for a municipal governmental agency that needs to track the
current year project costs against the current year project budget. If the
costs exceed the budget, they will need approval from elected officials to
transfer budget from a project with excess authority to the one with
insufficient budget authority.

 

[John]

I want to write a formula to show project costs for only the current year.
Can anybody help me out?

I'm writing this for a municipal governmental agency that needs to track the
current year project costs against the current year project budget. If the
costs exceed the budget, they will need approval from elected officials to
transfer budget from a project with excess authority to the one with
insufficient budget authority.

Steve,
It MAY be possible to get pretty close with a formula in a custom field
if your tasks are such that they do not cross the year end barriers. In
other words, a formula could sum up all costs for tasks with a Start
date greater than 1/1/xx and a Finish date before 12/31/xx. However,
most project plan do not have that clean a break at year end. A much
better approach would be to sum up the time-phased data between the
year's bounding dates (that might be a fiscal year in your case), and
then dump that value into a spare cost field. This can easily be done
with a VBA macro using the TimeScaleData Method.

Another approach that does not require VBA or a formula is to use the
"Analyze timescale data in Excel" utility/add-in (found on the Analysis
toolbar). Once the data is in Excel, it is much easier to manipulate.

Hope this helps.
John
Project MVP

 

[Steve K]

John, thanks for the reply. Can you point me towards any literature
discussing the "TimeScaleData Method"?

 

[John]

John, thanks for the reply. Can you point me towards any literature
discussing the "TimeScaleData Method"?

Steve,
The best place to look is in the VBA help file. It will give you the
full description and syntax for using the TimescaleDate Method.

If you are looking for a more in-depth tutorial on Project VBA, I
suggest you go to out MVP website at:
http://www.mvps.org/project/links.htm
and look for the link at the bottom of the page called, "Project 98
Visual Basic Environment Training Materials". Even though it says it is
for Project 98, it is equally applicable to all current versions of
Project.

Hope this helps.
John
Project MVP

 

[Steve K]

John, that's tremendous help. Thanks so much.

Steve,
The best place to look is in the VBA help file. It will give you the
full description and syntax for using the TimescaleDate Method.

If you are looking for a more in-depth tutorial on Project VBA, I
suggest you go to out MVP website at:
http://www.mvps.org/project/links.htm
and look for the link at the bottom of the page called, "Project 98
Visual Basic Environment Training Materials". Even though it says it is
for Project 98, it is equally applicable to all current versions of
Project.

Hope this helps.
John
Project MVP

 

[John]

John, that's tremendous help. Thanks so much.

Steve,
Great! I love it when we can help a user score a touchdown (seasonal
remark).

John