Formulas for Custom Fields

[dave]

Anybody know a site that can give examples of the code required for the above ?

Primarily I'm looking to report on tasks via a custom field using a grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I want to add a
formula that dictates what that value is i.e. - If %complete = 100, then
value = b, If end > today then value = r, if end date > today-3 & & Complete

50, then value = y, else value = g.

Any ideas please !

 

[JulieS]

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG" you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of articles on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required for
the above ?

Primarily I'm looking to report on tasks via a custom field using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I want to
add a
formula that dictates what that value is i.e. - If %complete = 100,
then
value = b, If end > today then value = r, if end date > today-3 & &
Complete

50, then value = y, else value = g.

Any ideas please !

 

[dave]

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have entered a
basic 'function' that changes the colour based on the % complete [ either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other status, and
add in another field to test on [ end date - so if the task is >75% complete
and the end date = <[today+5] "Y".

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG" you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of articles on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required for
the above ?

Primarily I'm looking to report on tasks via a custom field using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I want to
add a
formula that dictates what that value is i.e. - If %complete = 100,
then
value = b, If end > today then value = r, if end date > today-3 & &
Complete

50, then value = y, else value = g.

Any ideas please !

 

[JulieS]

Hi Dave,

Why don't you post the formula you have so far and we'll see if we can
give you the missing pieces.

Julie

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have
entered a
basic 'function' that changes the colour based on the % complete [
either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other status,
and
add in another field to test on [ end date - so if the task is >75%
complete
and the end date = <[today+5] "Y".

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG"
you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of articles
on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required for
the above ?

Primarily I'm looking to report on tasks via a custom field using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I want
to
add a
formula that dictates what that value is i.e. - If %complete = 100,
then
value = b, If end > today then value = r, if end date > today-3 & &
Complete
50, then value = y, else value = g.

Any ideas please !

 

[dave]

Julie,
Here's what I have so far...

Switch([% Complete]=100,"b",[% Complete]<50,"r",[%
Complete]<75,"y",[Start]<[Date1],"g")

The "blue" bit works, as do the "red"&"yellow", except now I have to qualify
them.

If it's less than 50% complete and the finish date is less than today
[date1] then red.
If it's greater than 75% but less than 100% then Yellow, if it hasn't
started - less than [date1] then green.

I know there's a few gaps but once I get the basic syntax right I can amend
to fit the requirement.

Thanks for your help - a virtual ice cream will be your reward !

Hi Dave,

Why don't you post the formula you have so far and we'll see if we can
give you the missing pieces.

Julie

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have
entered a
basic 'function' that changes the colour based on the % complete [
either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other status,
and
add in another field to test on [ end date - so if the task is >75%
complete
and the end date = <[today+5] "Y".

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG"
you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of articles
on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required for
the above ?

Primarily I'm looking to report on tasks via a custom field using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I want
to
add a
formula that dictates what that value is i.e. - If %complete = 100,
then
value = b, If end > today then value = r, if end date > today-3 & &
Complete
50, then value = y, else value = g.

Any ideas please !

 

[JulieS]

Hi Dave,

You can add AND conditions to your tests. So, for example: the portion
about less than 50% and finish is less than today could be written:
[% Complete]<= 50 AND [Finish] < [Current Date]

What I have entered so far is below:
Switch([% Complete]=100,"Blue",[% Complete]>=75,"Yellow",[%
Complete]<=50 And [Finish]<[Current Date],"Red",[Start]>[Current
Date],"Green")

The problem that I see currently is you have no definition for tasks
between 50% and 75% complete or if the task is less than or equal to 50%
complete but the Finish date is greater than the current date. So if a
task is anywhere between 51 and 74 the formula returns an error.

The Switch command only evaluates to "True" if all parts of the test are
true, so you need to either set up a test so all parts evaluate to
"true" or use the IIf command which allows for false.

I hope this is helpful. Let us know how you get along.

Julie

Julie,
Here's what I have so far...

Switch([% Complete]=100,"b",[% Complete]<50,"r",[%
Complete]<75,"y",[Start]<[Date1],"g")

The "blue" bit works, as do the "red"&"yellow", except now I have to
qualify
them.

If it's less than 50% complete and the finish date is less than today
[date1] then red.
If it's greater than 75% but less than 100% then Yellow, if it hasn't
started - less than [date1] then green.

I know there's a few gaps but once I get the basic syntax right I can
amend
to fit the requirement.

Thanks for your help - a virtual ice cream will be your reward !

Hi Dave,

Why don't you post the formula you have so far and we'll see if we
can
give you the missing pieces.

Julie

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have
entered a
basic 'function' that changes the colour based on the % complete [
either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other
status,
and
add in another field to test on [ end date - so if the task is >75%
complete
and the end date = <[today+5] "Y".

:

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG"
you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of
articles
on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know
and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required
for
the above ?

Primarily I'm looking to report on tasks via a custom field
using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I
want
to
add a
formula that dictates what that value is i.e. - If %complete =
100,
then
value = b, If end > today then value = r, if end date > today-3
& &
Complete
50, then value = y, else value = g.

Any ideas please !

 

[dave]

I did think of using the IIf command, as that seemed more logical [ and like
VB ], however it seemed to me that that particular function would only allow
1 true expression, if you can have multiple true exressions, then one false
if none of the expressions are true then that would be better for me.

It's a shame that this particular part of MSP is so clumsy as it's a great
way of highlighting info on a plan.

Is there a way of using the IIf function and what would a similar syntax be ?

Thanks

Hi Dave,

You can add AND conditions to your tests. So, for example: the portion
about less than 50% and finish is less than today could be written:
[% Complete]<= 50 AND [Finish] < [Current Date]

What I have entered so far is below:
Switch([% Complete]=100,"Blue",[% Complete]>=75,"Yellow",[%
Complete]<=50 And [Finish]<[Current Date],"Red",[Start]>[Current
Date],"Green")

The problem that I see currently is you have no definition for tasks
between 50% and 75% complete or if the task is less than or equal to 50%
complete but the Finish date is greater than the current date. So if a
task is anywhere between 51 and 74 the formula returns an error.

The Switch command only evaluates to "True" if all parts of the test are
true, so you need to either set up a test so all parts evaluate to
"true" or use the IIf command which allows for false.

I hope this is helpful. Let us know how you get along.

Julie

Julie,
Here's what I have so far...

Switch([% Complete]=100,"b",[% Complete]<50,"r",[%
Complete]<75,"y",[Start]<[Date1],"g")

The "blue" bit works, as do the "red"&"yellow", except now I have to
qualify
them.

If it's less than 50% complete and the finish date is less than today
[date1] then red.
If it's greater than 75% but less than 100% then Yellow, if it hasn't
started - less than [date1] then green.

I know there's a few gaps but once I get the basic syntax right I can
amend
to fit the requirement.

Thanks for your help - a virtual ice cream will be your reward !

Hi Dave,

Why don't you post the formula you have so far and we'll see if we
can
give you the missing pieces.

Julie

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have
entered a
basic 'function' that changes the colour based on the % complete [
either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other
status,
and
add in another field to test on [ end date - so if the task is >75%
complete
and the end date = <[today+5] "Y".

:

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG"
you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of
articles
on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know
and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code required
for
the above ?

Primarily I'm looking to report on tasks via a custom field
using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now I
want
to
add a
formula that dictates what that value is i.e. - If %complete =
100,
then
value = b, If end > today then value = r, if end date > today-3
& &
Complete
50, then value = y, else value = g.

Any ideas please !

 

[JulieS]

Hi Dave,

The format of the IIf is
IIf( expression, truepart, falsepart)

You can nest multiple other tests into the falsepart. For example:
IIf(expression1, truepart1, IIf(expression2, truepart2, IIf(expression3,
truepart3,falsepart)))

I agree that the examples in help are not fully enough explained (in my
opinion) so it takes a bit of bumping around to get it just right.

I hope this helps. Let us know how you get on ;-)

Julie

I did think of using the IIf command, as that seemed more logical [ and
like
VB ], however it seemed to me that that particular function would only
allow
1 true expression, if you can have multiple true exressions, then one
false
if none of the expressions are true then that would be better for me.

It's a shame that this particular part of MSP is so clumsy as it's a
great
way of highlighting info on a plan.

Is there a way of using the IIf function and what would a similar
syntax be ?

Thanks

Hi Dave,

You can add AND conditions to your tests. So, for example: the
portion
about less than 50% and finish is less than today could be written:
[% Complete]<= 50 AND [Finish] < [Current Date]

What I have entered so far is below:
Switch([% Complete]=100,"Blue",[% Complete]>=75,"Yellow",[%
Complete]<=50 And [Finish]<[Current Date],"Red",[Start]>[Current
Date],"Green")

The problem that I see currently is you have no definition for tasks
between 50% and 75% complete or if the task is less than or equal to
50%
complete but the Finish date is greater than the current date. So if
a
task is anywhere between 51 and 74 the formula returns an error.

The Switch command only evaluates to "True" if all parts of the test
are
true, so you need to either set up a test so all parts evaluate to
"true" or use the IIf command which allows for false.

I hope this is helpful. Let us know how you get along.

Julie

Julie,
Here's what I have so far...

Switch([% Complete]=100,"b",[% Complete]<50,"r",[%
Complete]<75,"y",[Start]<[Date1],"g")

The "blue" bit works, as do the "red"&"yellow", except now I have
to
qualify
them.

If it's less than 50% complete and the finish date is less than
today
[date1] then red.
If it's greater than 75% but less than 100% then Yellow, if it
hasn't
started - less than [date1] then green.

I know there's a few gaps but once I get the basic syntax right I
can
amend
to fit the requirement.

Thanks for your help - a virtual ice cream will be your reward !

:

Hi Dave,

Why don't you post the formula you have so far and we'll see if we
can
give you the missing pieces.

Julie

Thanks Julie,
This gets me 75% of the way there.

I've set up a text field that has grafical indicators, and have
entered a
basic 'function' that changes the colour based on the % complete
[
either
blue for 100 else green ]
What I now need to do is expand the formula to include 2 other
status,
and
add in another field to test on [ end date - so if the task is
75%
complete
and the end date = <[today+5] "Y".

:

Hi Dave,

If you search this newsgroup for "Stop Light Reports" or "RAG"
you'll
find multiple examples of custom formulas and how to set up the
graphical indicators in the custom formulas.

You might also check out fellow MVP, Mike Glen's series of
articles
on
MS Project at:
http://project.mvps.org/mike's_tutorials.htm

Article 15 steps through graphical indicators.


Take a look at those and see if they help. If not, let us know
and
we'll jump in.

I hope this helps. Let us know how you get along.

Julie

Anybody know a site that can give examples of the code
required
for
the above ?

Primarily I'm looking to report on tasks via a custom field
using a
grafical
indicator [ BRAG ].
I've set the field to display a colour based on a value, now
I
want
to
add a
formula that dictates what that value is i.e. - If %complete
=
100,
then
value = b, If end > today then value = r, if end date >
today-3
& &
Complete
50, then value = y, else value = g.

Any ideas please !