Formulas and functions

[Cynthis PMP]

I would like to do the following:
If a task is 50 percent into the duration and the percent complete is not 50
percent or higher turn a custom field to 'yellow' or if a task is 75 perecent
into the duration and the percent complete is not 75 percent or higher turn a
custom field to 'red' else make the custom field 'green'. I cannot figure
out how to write this.

 

[John]

I would like to do the following:
If a task is 50 percent into the duration and the percent complete is not 50
percent or higher turn a custom field to 'yellow' or if a task is 75 perecent
into the duration and the percent complete is not 75 percent or higher turn a
custom field to 'red' else make the custom field 'green'. I cannot figure
out how to write this.

Cynthis,
Without actually having the time to develop the complete formula (this
or similar questions have been asked previously - a search of past
newsgroup posting should yield an exact answer), here is the basic
approach.

I would probably use VBA but a compound "if" statement in a custom field
formula should also work (although it may require a couple of custom
fields). I would subtract the Task Start date from the Now() function
(using ProjDatSub) and form a ratio with the task duration. Then compare
that decimal value to the decimal equivalent of the % Complete field and
set the indicators based on your parameters.

Hope this helps.
John
Project MVP

 

[Cynthis PMP]

I have tried coming up with the formula and cannot master it. I also have
read many posting (probably 50 or more) and cannot find this answer. I do
not know VB so I could not write it. Would you be able to assist me or give
me a book that would have an example like this?

Thank you very much

 

[John]

I have tried coming up with the formula and cannot master it. I also have
read many posting (probably 50 or more) and cannot find this answer. I do
not know VB so I could not write it. Would you be able to assist me or give
me a book that would have an example like this?

Thank you very much

Cynthis,
As I indicated, I am more comfortable with VBA than complex formulas (my
head starts to spin). I have never used graphical indicators with VBA so
this was a learning experience for me too.

The macro is set up to use spare Task field Number1 to display the
indicators (I chose a simple sphere). You didn't indicate what you
wanted to do with Summary Lines. Since % Complete for Summary Lines is
calculated by Project there is always some argument as to its validity.
As written indicators for Summary Lines are not displayed. If you want
indicators for Summary Lines, simply comment out (insert an apostrophe
at the beginning of the line) the first line of code and remove the
comment indicator from the second line of code.

Herewith is the macro. If you need help in setting it up and running it,
let me know. Note: When you copy and paste the code into the VB editor
pay attention to line continuation indicators (i.e. space and
underscore).

Sub DurIndicators()
CustomFieldProperties FieldID:=pjCustomTaskNumber1,
attribute:=pjFieldAttributeNone, _
graphicalindicators:=True
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
CurVal = t.PercentComplete / 100
shouldbe = Application.DateDifference(t.Start,
ActiveProject.CurrentDate)
ratio = shouldbe / t.Duration
t.Number1 = 3
If ratio >= 0.5 And ratio < 0.75 And CurVal < ratio Then
t.Number1 = 1
If ratio >= 0.75 And CurVal < ratio Then t.Number1 = 2

CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber1,
test:=pjCompareEquals, _
Value:=1, indicatorID:=pjIndicatorSphereYellow
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber1,
test:=pjCompareEquals, _
Value:=2, indicatorID:=pjIndicatorSphereRed
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber1,
test:=pjCompareEquals, _
Value:=3, indicatorID:=pjIndicatorSphereGreen
End If
Next t
End Sub

Hope this helps.
John
Project MVP