Custom Field Formulas

[Jumpinjackflash]

Please help!
I need to display a custom field that converts work hours into a head count
estimation for budgeting purposes. I am using Project 2000.

I have 2 problems: 1) I do not see a rounding function in microsoft project.
Am I missing something? My field calculates, for example, a head count of
4.39, but I would like it to just show 4.4.
2) How do I display my head count custom calculation data in the timephased
field (right hand side) of the resource usage view? I understand how you can
change the data shown to other fields, like actual work, % complete, etc.,
but how can you display data from a custom field?

 

[John]

Please help!
I need to display a custom field that converts work hours into a head count
estimation for budgeting purposes. I am using Project 2000.

I have 2 problems: 1) I do not see a rounding function in microsoft project.
Am I missing something? My field calculates, for example, a head count of
4.39, but I would like it to just show 4.4.
2) How do I display my head count custom calculation data in the timephased
field (right hand side) of the resource usage view? I understand how you can
change the data shown to other fields, like actual work, % complete, etc.,
but how can you display data from a custom field?

Jumpin,
The "rounding function" is effectively embedded in the Format statement.
For example the following formula will do what you need:
Text1 = Format([Text2],"#00.0")
In your case Text2 would probably be your headcount formula.

Unfortunately custom fields cannot easily be timescaled. To do so would
require a special VBA processing algorithm and then the data would need
to either be spread across several spare fields (one for each time
period) or better yet, exported to another application (e.g. Excel). I
have done the latter many times.

Just for reference, how are you calculating headcount? Each month has a
different number of days (calendar or working) and unless that value is
also stored in a spare field that is used in the calculation, the
headcount values won't really be accurate anyway.

Hope this helps.
John
Project MVP

 

[Jumpinjackflash]

Thanks for the useful information.
It seems that the format function only works for text fields, not custom
fields containing numeric data. I also can't find help information on how
you display the desired format (in my case one decimal place for head count).

How difficult is the VBA algorithm, and do you know where I can find that
information? I have tried copying timephase work data into excel to
determine head count (I have a formula in Excel to remove the h's and convert
the data to numeric), but I was hoping to track headcount directly in Project.

I was just calculating a headcount per quarter to see if I could get this
custom feature to work initially - we have a standard hours per head count
per quarter that we use at my company. But ideally, I would have just one
head count custom and the calculation would vary on the timescale selected in
the timephase view. Is that doable?

Please help!
I need to display a custom field that converts work hours into a head count
estimation for budgeting purposes. I am using Project 2000.

I have 2 problems: 1) I do not see a rounding function in microsoft project.
Am I missing something? My field calculates, for example, a head count of
4.39, but I would like it to just show 4.4.
2) How do I display my head count custom calculation data in the timephased
field (right hand side) of the resource usage view? I understand how you can
change the data shown to other fields, like actual work, % complete, etc.,
but how can you display data from a custom field?

Jumpin,
The "rounding function" is effectively embedded in the Format statement.
For example the following formula will do what you need:
Text1 = Format([Text2],"#00.0")
In your case Text2 would probably be your headcount formula.

Unfortunately custom fields cannot easily be timescaled. To do so would
require a special VBA processing algorithm and then the data would need
to either be spread across several spare fields (one for each time
period) or better yet, exported to another application (e.g. Excel). I
have done the latter many times.

Just for reference, how are you calculating headcount? Each month has a
different number of days (calendar or working) and unless that value is
also stored in a spare field that is used in the calculation, the
headcount values won't really be accurate anyway.

Hope this helps.
John
Project MVP

 

[JackD]

Yes, the format function works in text fields only. But why not use a text
field?
The VBA algorithm could be rather complicated depending on what you want to
do.
What is the calculation you are using and what do you want to achieve?

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

Thanks for the useful information.
It seems that the format function only works for text fields, not custom
fields containing numeric data. I also can't find help information on how
you display the desired format (in my case one decimal place for head count).

How difficult is the VBA algorithm, and do you know where I can find that
information? I have tried copying timephase work data into excel to
determine head count (I have a formula in Excel to remove the h's and convert
the data to numeric), but I was hoping to track headcount directly in Project.

I was just calculating a headcount per quarter to see if I could get this
custom feature to work initially - we have a standard hours per head count
per quarter that we use at my company. But ideally, I would have just one
head count custom and the calculation would vary on the timescale selected in
the timephase view. Is that doable?

Please help!
I need to display a custom field that converts work hours into a head count
estimation for budgeting purposes. I am using Project 2000.

I have 2 problems: 1) I do not see a rounding function in microsoft project.
Am I missing something? My field calculates, for example, a head count of
4.39, but I would like it to just show 4.4.
2) How do I display my head count custom calculation data in the timephased
field (right hand side) of the resource usage view? I understand how you can
change the data shown to other fields, like actual work, % complete, etc.,
but how can you display data from a custom field?

Jumpin,
The "rounding function" is effectively embedded in the Format statement.
For example the following formula will do what you need:
Text1 = Format([Text2],"#00.0")
In your case Text2 would probably be your headcount formula.

Unfortunately custom fields cannot easily be timescaled. To do so would
require a special VBA processing algorithm and then the data would need
to either be spread across several spare fields (one for each time
period) or better yet, exported to another application (e.g. Excel). I
have done the latter many times.

Just for reference, how are you calculating headcount? Each month has a
different number of days (calendar or working) and unless that value is
also stored in a spare field that is used in the calculation, the
headcount values won't really be accurate anyway.

Hope this helps.
John
Project MVP

 

[John]

Thanks for the useful information.
It seems that the format function only works for text fields, not custom
fields containing numeric data. I also can't find help information on how
you display the desired format (in my case one decimal place for head count).

How difficult is the VBA algorithm, and do you know where I can find that
information? I have tried copying timephase work data into excel to
determine head count (I have a formula in Excel to remove the h's and convert
the data to numeric), but I was hoping to track headcount directly in
Project.

I was just calculating a headcount per quarter to see if I could get this
custom feature to work initially - we have a standard hours per head count
per quarter that we use at my company. But ideally, I would have just one
head count custom and the calculation would vary on the timescale selected in
the timephase view. Is that doable?

Junpin,
I think Jack answered your questions but I'll throw in my comments also.

Values in spare number fields can be rounded also but I wouldn't want to
try doing it with a custom field formula (too complex) - I would use
VBA. But I agree with Jack, why not use a text field with the Format
function as I suggested?

The VBA algorithm isn't necessarily difficult but it does require two
basic things - knowledge of Project VBA and a good clear definition of
what is needed so the algorithm can be developed. For example, to create
timescaled data for the data in a spare field, the algorithm needs to
know if the data is to be spread linearly over the span or whether it
needs to be contoured (e.g. front loaded, bell curve, etc.). A linear
spread is pretty straight forward - contoured data is a little more
complex. What I have done many times, is to take basic Project data
(e.g. resource hours), manipulate it (e.g. create headcount based on
calendar hours per month), and export it to Excel in a format that shows
it by task, by resource, by month, or whatever.

Hope this helps.
John
Project MVP

 

[Jan De Messemaeker]

Hi,

Just a precision here to avoid useless searching
When John writes (quote)
"For example, to create
timescaled data for the data in a spare field,"

One must realize that these timescaled data can only reside outside Project
(f.i. in Excel) OR the "Spare Field" must be one in which Project HAS
foreseen timescaled data.

For instance, it is not possible to create and show timescaled data in a
Number field.

Hope this helps,

 

[John]

Hi,

Just a precision here to avoid useless searching
When John writes (quote)
"For example, to create
timescaled data for the data in a spare field,"

One must realize that these timescaled data can only reside outside Project
(f.i. in Excel) OR the "Spare Field" must be one in which Project HAS
foreseen timescaled data.

For instance, it is not possible to create and show timescaled data in a
Number field.

Hope this helps,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620

Jan,
True, although I mentioned that in my original post - perhaps I wasn't
specific enough. To be precise the data CAN be displayed in Project (".
.. . across several spare fields (one for each time period). . .")
although as I mentioned, this method is not very convenient.

John
Project MVP