Creating a Traffic Stop field for a Project Template aka project status

[texmaster]

Hi,

Not really new to project but custom fields absolutely. For most projects, I keep it simple with Start, Finish, Hours etc.

What I really need is a field I'm guessing formula that will act as a traffic stop light depending on how close or over a task is from the "Finish" field for example.


Ie for green, that would be for tasks 5 days or more away from the finish date
for yellow, under 5 days from finish
for red, over the finish date
and blue for completed tasks.

Have you looked at the built in Status or Status Indicator field?

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

What are you using to compare to the finish date? Are you comparing Finish
to Baseline Finish or are you comparing where the actual has currently been
progressed to i.e. [Stop] and comparing that to your [Finish]?

I think I can provide you with a formula and instructions if you can clarify
this point.

[texmaster]

For now, I'm comparing it the current date whatever that is when its opened. My group is new to project and they want the ability to open a project, look at the current date structure and basically, look for yellow and pay attention to those tasks.

I'm sorry to say for a group of 8 guys, we have over 35 active projects and I can't get management to purchase project server this year.

For example,

Many of my projects for now have task name, duration, start finish and predecessors along with percentage complete and thats it.

I would like to move to what you are proposing having a finish date and actual finish date to compare when tasks are actually completed.

Sorry if I'm being cryptic.

Thanks for any help you can provide.

I'd be curious to know why the built-in status fields Julie mentions are
not sufficient for your need? What's better about the bespoke method
you plan?

For clarification, it appears as though you want to compare the
Baseline Finish to the current date -- if the Baseline finish is
greater than 5 days away it's green. If the Baseline Finish is less
than 5 days away, yellow, if the Baseline finish is earlier than
today, red, and if the task is finished, blue.

So, to get you started. In a spare text field create the following
formula

IIf([% Complete]=100,"Complete",IIf([Baseline
Finish]=ProjDateValue("NA"),"No Baseline",IIf(DateDiff("d",[Current
Date],[Baseline Finish])>=5,"Green",IIf(DateDiff("d",[Current
Date],[Baseline Finish])>=0,"Yellow","Red"))))

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

Sorry, I should have added the final polish -- in the customize
fields dialog, set the graphic indicators to correspond with the
calculated text value.

I have a white paper on my blog about this. Find all the tedium there, link
is below along with all the formulas and some different thoughts.

[texmaster]

I'm sorry to be so clueless.

When I try to put the formula in, it fails claiming Syntax field and highlights "Current Date"

As far as the graphic indicators, I'm lost there as well.

What goes line by line? for all colors?

This should work for you, it works fine on my machine.

Create a custom TASK level TEXT field with the following formula:

IIf([% Complete]=100,"Blue",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480<0,"Red",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480<5,"Yellow",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480>=5,"Green"))))

Set "Calculation for task and group summary rows" to "Formula"

For values to display in Graphical Indicators do the following:
Under Nonsummary rows add 4 values

Test for 'text1' Value(s) Image
contains Blue [set to a blue circle]
contains Green [set to a green circle]
contains Yellow [set to a yellow circle]
contains Red [set to a red circle]

For Summary rows check the box to inherit from nonsummary rows
For Project summary rows check the box to inherit from summary rows
Check the box "Display values in tooltips" (so when you hove the mouse of
the image it shows the text behind it.

Now, if you want to in the formula, you can add more text so instead of just
"Red", you can say "Red, task was due to finish in the past". The key is to
keep the word "Red" in the text because that is what your graphical
indicator looks for (i.e. contains Red)

Hope this helps,

Jonathan Sofer

"texmaster" <texmaster.3ztgrb@DoNotSpam.com> wrote in message
news:texmaster.3ztgrb@DoNotSpam.com...
>
> Hi Jonathan,
>
> For now, I'm comparing it the current date whatever that is when its
> opened. My group is new to project and they want the ability to open a
> project, look at the current date structure and basically, look for
> yellow and pay attention to those tasks.
>
> I'm sorry to say for a group of 8 guys, we have over 35 active projects
> and I can't get management to purchase project server this year.
>
> For example,
>
> Many of my projects for now have task name, duration, start finish and
> predecessors along with percentage complete and thats it.
>
> I would like to move to what you are proposing having a finish date and
> actual finish date to compare when tasks are actually completed.
>
> Sorry if I'm being cryptic.
>
> Thanks for any help you can provide.
>
>
> 'Jonathan Sofer [MVP Wrote:
>> ;4645938']What are you using to compare to the finish date? Are you
>> comparing Finish
>> to Baseline Finish or are you comparing where the actual has currently
>> been
>> progressed to i.e. [Stop] and comparing that to your [Finish]?
>>
>> I think I can provide you with a formula and instructions if you can
>> clarify
>> this point.
>>
>> Jonathan
>>
>> "texmaster" <texmaster.3zt5nc@DoNotSpam.com> wrote in message
>> news:texmaster.3zt5nc@DoNotSpam.com...
>> >
>> > Hi,
>> >
>> > Not really new to project but custom fields absolutely. For most
>> > projects, I keep it simple with Start, Finish, Hours etc.
>> >
>> > What I really need is a field I'm guessing formula that will act as
>> a
>> > traffic stop light depending on how close or over a task is from the
>> > "Finish" field for example.
>> >
>> >
>> > Ie for green, that would be for tasks 5 days or more away from the
>> > finish date
>> > for yellow, under 5 days from finish
>> > for red, over the finish date
>> > and blue for completed tasks.
>> >
>> >
>> > --
>> > texmaster
>> >
>> ------------------------------------------------------------------------
>> > texmaster's Profile: http://forums.techarena.in/members/142408.htm
>> > View this thread:
>> Creating a Traffic Stop field for a Project Template aka project status
>> >
>> > http://forums.techarena.in
>> >
>
>
> --
> texmaster
> ------------------------------------------------------------------------
> texmaster's Profile: http://forums.techarena.in/members/142408.htm
> View this thread: Creating a Traffic Stop field for a Project Template aka project status
>
> http://forums.techarena.in
>

[texmaster]

Originally Posted by Rob Schneider

I'd be curious to know why the built-in status fields Julie mentions are
not sufficient for your need? What's better about the bespoke method
you plan?

I didn't see any that actually automatically calculated.

Yes, those fields are automatically calculated and fully documented.
Insert them onto the view, right mouse click the column heading to get a
link to the Help. Most of what is needed by Project is generally already
there.

--rms

www.rmschneider.com





texmaster wrote:
> Rob Schneider;4646308 Wrote:
>> I'd be curious to know why the built-in status fields Julie mentions
>> are
>> not sufficient for your need? What's better about the bespoke method
>> you plan?
>
> I didn't see any that actually automatically calculated.
>
>

Hi texmaster,

Did you copy and paste the formula from below? It's possible that
by copying and pasting you've picked up a spare return. Try
copy/paste into note pad first and then edit the formula to remove
any returns.

To your question about the graphic indicators, once you've check
that the formula works -- you're seeing the correct word showing in
the column, right click and choose "Customize field again. Click
the Graphical Indicators button and enter the test, one for each
line.

So, if you've used Text1
Test: equals
Value: Green
Image: Green circle

Next row
Test: equals
Value: Yellow
Image: yellow circle

Each one of the values (Complete, No baseline, Green, Red, Yellow)
should have an image or your can leave the image value blank to show
nothing.

And don't apologize for being "clueless" -- you're far from it :-)

Julie
Project MVP
"texmaster" <texmaster.3zwuzc@DoNotSpam.com> wrote in message
news:texmaster.3zwuzc@DoNotSpam.com...
>
> I'm sorry to be so clueless.
>
> When I try to put the formula in, it fails claiming Syntax field
> and
> highlights "Current Date"
>
> As far as the graphic indicators, I'm lost there as well.
>
> What goes line by line? for all colors?
>
>
> JulieS;4646665 Wrote:
>> Sorry, I should have added the final polish -- in the customize
>> fields dialog, set the graphic indicators to correspond with the
>> calculated text value.
>>
>> "JulieS" <JulieS@discussions.microsoft.com> wrote in message
>> news:OLk62SbSKHA.4028@TK2MSFTNGP05.phx.gbl...
>> > Hello texmaster,
>> >
>> > For clarification, it appears as though you want to compare the
>> > Baseline Finish to the current date -- if the Baseline finish
>> > is
>> > greater than 5 days away it's green. If the Baseline Finish is
>> > less than 5 days away, yellow, if the Baseline finish is
>> > earlier
>> > than today, red, and if the task is finished, blue.
>> >
>> > So, to get you started. In a spare text field create the
>> > following formula
>> >
>> > IIf([% Complete]=100,"Complete",IIf([Baseline
>> > Finish]=ProjDateValue("NA"),"No
>> > Baseline",IIf(DateDiff("d",[Current Date],[Baseline
>> > Finish])>=5,"Green",IIf(DateDiff("d",[Current Date],[Baseline
>> > Finish])>=0,"Yellow","Red"))))
>> >
>> > I hope this helps. Let us know how you get along.
>> >
>> > Julie
>> > Project MVP
>> >
>> > Visit http://project.mvps.org/ for the FAQs and additional
>> > information about Microsoft Project
>> >
>> > "texmaster" <texmaster.3ztgrb@DoNotSpam.com> wrote in message
>> > news:texmaster.3ztgrb@DoNotSpam.com...
>> >>
>> >> Hi Jonathan,
>> >>
>> >> For now, I'm comparing it the current date whatever that is
>> >> when
>> >> its
>> >> opened. My group is new to project and they want the ability
>> >> to
>> >> open a
>> >> project, look at the current date structure and basically,
>> >> look
>> >> for
>> >> yellow and pay attention to those tasks.
>> >>
>> >> I'm sorry to say for a group of 8 guys, we have over 35 active
>> >> projects
>> >> and I can't get management to purchase project server this
>> >> year.
>> >>
>> >> For example,
>> >>
>> >> Many of my projects for now have task name, duration, start
>> >> finish and
>> >> predecessors along with percentage complete and thats it.
>> >>
>> >> I would like to move to what you are proposing having a finish
>> >> date and
>> >> actual finish date to compare when tasks are actually
>> >> completed.
>> >>
>> >> Sorry if I'm being cryptic.
>> >>
>> >> Thanks for any help you can provide.
>> >>
>> >>
>> >> 'Jonathan Sofer [MVP Wrote:
>> >>> ;4645938']What are you using to compare to the finish date?
>> >>> Are
>> >>> you
>> >>> comparing Finish
>> >>> to Baseline Finish or are you comparing where the actual has
>> >>> currently
>> >>> been
>> >>> progressed to i.e. [Stop] and comparing that to your
>> >>> [Finish]?
>> >>>
>> >>> I think I can provide you with a formula and instructions if
>> >>> you
>> >>> can
>> >>> clarify
>> >>> this point.
>> >>>
>> >>> Jonathan
>> >>>
>> >>> "texmaster" <texmaster.3zt5nc@DoNotSpam.com> wrote in message
>> >>> news:texmaster.3zt5nc@DoNotSpam.com...
>> >>> >
>> >>> > Hi,
>> >>> >
>> >>> > Not really new to project but custom fields absolutely.
>> >>> > For
>> >>> > most
>> >>> > projects, I keep it simple with Start, Finish, Hours etc.
>> >>> >
>> >>> > What I really need is a field I'm guessing formula that
>> >>> > will
>> >>> > act as
>> >>> a
>> >>> > traffic stop light depending on how close or over a task is
>> >>> > from the
>> >>> > "Finish" field for example.
>> >>> >
>> >>> >
>> >>> > Ie for green, that would be for tasks 5 days or more away
>> >>> > from
>> >>> > the
>> >>> > finish date
>> >>> > for yellow, under 5 days from finish
>> >>> > for red, over the finish date
>> >>> > and blue for completed tasks.
>> >>> >
>> >>> >
>> >>> > --
>> >>> > texmaster
>> >>> >
>> >>>
>> ------------------------------------------------------------------------
>> >>> > texmaster's Profile:
>> >>> > http://forums.techarena.in/members/142408.htm
>> >>> > View this thread:
>> >>> Creating a Traffic Stop field for a Project Template aka project status
>> >>> >
>> >>> > http://forums.techarena.in
>> >>> >
>> >>
>> >>
>> >> --
>> >> texmaster
>> >>
>> ------------------------------------------------------------------------
>> >> texmaster's Profile:
>> >> http://forums.techarena.in/members/142408.htm
>> >> View this thread:
>> >> Creating a Traffic Stop field for a Project Template aka project status
>> >>
>> >> http://forums.techarena.in
>> >>
>> >
>> >
>
>
> --
> texmaster
> ------------------------------------------------------------------------
> texmaster's Profile: http://forums.techarena.in/members/142408.htm
> View this thread:
> Creating a Traffic Stop field for a Project Template aka project status
>
> http://forums.techarena.in
>

[texmaster]

Hi Jonathan,

For some reason every time I try to enter the formula I get a syntax error and it highlights "current date"

Am I not putting it in the right space? I'm going to "Custom Attributes" then clicking on "formula"

Originally Posted by Jonathan Sofer [MVP]

This should work for you, it works fine on my machine.

Create a custom TASK level TEXT field with the following formula:

IIf([% Complete]=100,"Blue",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480<0,"Red",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480<5,"Yellow",IIf(ProjDateDiff([Current
Date],[Finish],[Project Calendar])/480>=5,"Green"))))

Set "Calculation for task and group summary rows" to "Formula"

For values to display in Graphical Indicators do the following:
Under Nonsummary rows add 4 values

Test for 'text1' Value(s) Image
contains Blue [set to a blue circle]
contains Green [set to a green circle]
contains Yellow [set to a yellow circle]
contains Red [set to a red circle]

For Summary rows check the box to inherit from nonsummary rows
For Project summary rows check the box to inherit from summary rows
Check the box "Display values in tooltips" (so when you hove the mouse of
the image it shows the text behind it.

Now, if you want to in the formula, you can add more text so instead of just
"Red", you can say "Red, task was due to finish in the past". The key is to
keep the word "Red" in the text because that is what your graphical
indicator looks for (i.e. contains Red)

Hope this helps,

Jonathan Sofer

"texmaster" <texmaster.3ztgrb@DoNotSpam.com> wrote in message
news:texmaster.3ztgrb@DoNotSpam.com...
>
> Hi Jonathan,
>
> For now, I'm comparing it the current date whatever that is when its
> opened. My group is new to project and they want the ability to open a
> project, look at the current date structure and basically, look for
> yellow and pay attention to those tasks.
>
> I'm sorry to say for a group of 8 guys, we have over 35 active projects
> and I can't get management to purchase project server this year.
>
> For example,
>
> Many of my projects for now have task name, duration, start finish and
> predecessors along with percentage complete and thats it.
>
> I would like to move to what you are proposing having a finish date and
> actual finish date to compare when tasks are actually completed.
>
> Sorry if I'm being cryptic.
>
> Thanks for any help you can provide.
>
>
> 'Jonathan Sofer [MVP Wrote:
>> ;4645938']What are you using to compare to the finish date? Are you
>> comparing Finish
>> to Baseline Finish or are you comparing where the actual has currently
>> been
>> progressed to i.e. [Stop] and comparing that to your [Finish]?
>>
>> I think I can provide you with a formula and instructions if you can
>> clarify
>> this point.
>>
>> Jonathan
>>
>> "texmaster" <texmaster.3zt5nc@DoNotSpam.com> wrote in message
>> news:texmaster.3zt5nc@DoNotSpam.com...
>> >
>> > Hi,
>> >
>> > Not really new to project but custom fields absolutely. For most
>> > projects, I keep it simple with Start, Finish, Hours etc.
>> >
>> > What I really need is a field I'm guessing formula that will act as
>> a
>> > traffic stop light depending on how close or over a task is from the
>> > "Finish" field for example.
>> >
>> >
>> > Ie for green, that would be for tasks 5 days or more away from the
>> > finish date
>> > for yellow, under 5 days from finish
>> > for red, over the finish date
>> > and blue for completed tasks.
>> >
>> >
>> > --
>> > texmaster
>> >
>> ------------------------------------------------------------------------
>> > texmaster's Profile: http://forums.techarena.in/members/142408.htm
>> > View this thread:
>> Creating a Traffic Stop field for a Project Template aka project status
>> >
>> > http://forums.techarena.in
>> >
>
>
> --
> texmaster
> ------------------------------------------------------------------------
> texmaster's Profile: http://forums.techarena.in/members/142408.htm
> View this thread: Creating a Traffic Stop field for a Project Template aka project status
>
> http://forums.techarena.in
>

texmaster,

Check that you haven't picked up a return in the formula -- there
should be a space but not a return. Click between "current" and
"date" in your pasted formula. Hit delete until the words run
together. Then put a space back in.

Julie

"texmaster" <texmaster.3zyk3b@DoNotSpam.com> wrote in message
news:texmaster.3zyk3b@DoNotSpam.com...
>
> Hi Jonathan,
>
> For some reason every time I try to enter the formula I get a
> syntax
> error and it highlights "current date"
>
> Am I not putting it in the right space? I'm going to "Custom
> Attributes" then clicking on "formula"
>
>
>
> 'Jonathan Sofer [MVP Wrote:
>> ;4648777']This should work for you, it works fine on my machine.
>>
>> Create a custom TASK level TEXT field with the following formula:
>>
>> IIf([% Complete]=100,"Blue",IIf(ProjDateDiff([Current
>> Date],[Finish],[Project
>> Calendar])/480<0,"Red",IIf(ProjDateDiff([Current
>> Date],[Finish],[Project
>> Calendar])/480<5,"Yellow",IIf(ProjDateDiff([Current
>> Date],[Finish],[Project Calendar])/480>=5,"Green"))))
>>
>> Set "Calculation for task and group summary rows" to "Formula"
>>
>> For values to display in Graphical Indicators do the following:
>> Under Nonsummary rows add 4 values
>>
>> Test for 'text1' Value(s) Image
>> contains Blue [set to a blue circle]
>> contains Green [set to a green circle]
>> contains Yellow [set to a yellow circle]
>> contains Red [set to a red circle]
>>
>> For Summary rows check the box to inherit from nonsummary rows
>> For Project summary rows check the box to inherit from summary
>> rows
>> Check the box "Display values in tooltips" (so when you hove the
>> mouse
>> of
>> the image it shows the text behind it.
>>
>> Now, if you want to in the formula, you can add more text so
>> instead of
>> just
>> "Red", you can say "Red, task was due to finish in the past".
>> The key
>> is to
>> keep the word "Red" in the text because that is what your
>> graphical
>> indicator looks for (i.e. contains Red)
>>
>> Hope this helps,
>>
>> Jonathan Sofer
>>
>> "texmaster" <texmaster.3ztgrb@DoNotSpam.com> wrote in message
>> news:texmaster.3ztgrb@DoNotSpam.com...
>> >
>> > Hi Jonathan,
>> >
>> > For now, I'm comparing it the current date whatever that is
>> > when its
>> > opened. My group is new to project and they want the ability
>> > to
>> open a
>> > project, look at the current date structure and basically, look
>> > for
>> > yellow and pay attention to those tasks.
>> >
>> > I'm sorry to say for a group of 8 guys, we have over 35 active
>> projects
>> > and I can't get management to purchase project server this
>> > year.
>> >
>> > For example,
>> >
>> > Many of my projects for now have task name, duration, start
>> > finish
>> and
>> > predecessors along with percentage complete and thats it.
>> >
>> > I would like to move to what you are proposing having a finish
>> > date
>> and
>> > actual finish date to compare when tasks are actually
>> > completed.
>> >
>> > Sorry if I'm being cryptic.
>> >
>> > Thanks for any help you can provide.
>> >
>> >
>> > 'Jonathan Sofer [MVP Wrote:
>> >> ;4645938']What are you using to compare to the finish date?
>> >> Are
>> you
>> >> comparing Finish
>> >> to Baseline Finish or are you comparing where the actual has
>> currently
>> >> been
>> >> progressed to i.e. [Stop] and comparing that to your [Finish]?
>> >>
>> >> I think I can provide you with a formula and instructions if
>> >> you
>> can
>> >> clarify
>> >> this point.
>> >>
>> >> Jonathan
>> >>
>> >> "texmaster" <texmaster.3zt5nc@DoNotSpam.com> wrote in message
>> >> news:texmaster.3zt5nc@DoNotSpam.com...
>> >> >
>> >> > Hi,
>> >> >
>> >> > Not really new to project but custom fields absolutely.
>> >> > For
>> most
>> >> > projects, I keep it simple with Start, Finish, Hours etc.
>> >> >
>> >> > What I really need is a field I'm guessing formula that will
>> >> > act
>> as
>> >> a
>> >> > traffic stop light depending on how close or over a task is
>> >> > from
>> the
>> >> > "Finish" field for example.
>> >> >
>> >> >
>> >> > Ie for green, that would be for tasks 5 days or more away
>> >> > from
>> the
>> >> > finish date
>> >> > for yellow, under 5 days from finish
>> >> > for red, over the finish date
>> >> > and blue for completed tasks.
>> >> >
>> >> >
>> >> > --
>> >> > texmaster
>> >> >
>> >>
>> ------------------------------------------------------------------------
>> >> > texmaster's Profile:
>> http://forums.techarena.in/members/142408.htm
>> >> > View this thread:
>> >> Creating a Traffic Stop field for a Project Template aka project status
>> >> >
>> >> > http://forums.techarena.in
>> >> >
>> >
>> >
>> > --
>> > texmaster
>> >
>> ------------------------------------------------------------------------
>> > texmaster's Profile:
>> > http://forums.techarena.in/members/142408.htm
>> > View this thread:
>> Creating a Traffic Stop field for a Project Template aka project status
>> >
>> > http://forums.techarena.in
>> >
>
>
> --
> texmaster
> ------------------------------------------------------------------------
> texmaster's Profile: http://forums.techarena.in/members/142408.htm
> View this thread:
> Creating a Traffic Stop field for a Project Template aka project status
>
> http://forums.techarena.in
>