Ho Do You Flag Tasks That Are Due To Start

[Quirky]

I am trying to Flag tasks using Traffic light system to highlight what Tasks
are about to start (ie Green) what tasks are due to start in 1 Month (amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to make this
happen?

Any suggestions????

 

[John]

I am trying to Flag tasks using Traffic light system to highlight what Tasks
are about to start (ie Green) what tasks are due to start in 1 Month (amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to make this
happen?

Any suggestions????

Quirly,
Yes, that's what we are here for. In the following formula I assume you
mean 30 [working] days as one month and 90 [working] days as 3 months.
If you want to use different values, adjust the formula accordingly.

1. Insert a spare text field as a column into the view
2. Go to Tools/Customize/Fields
3. Enter the following formula into the field you selected
IIf([Start]<=now() And [Start]<now()+30,"green",IIf([Start]>=now()+30
And [Start]<now()+90,"amber",IIf([Start]>=now()+90,"red")))

4. Hit "OK"
5. While still in the Customize Fields window, select the Graphical
Indicators button
6. Enter the following info for graphical indicators
Test Value Image
contains green [green dot or whatever]
contains amber [next color]
contains red [last color]

7. Finally hit "OK" and "OK". You should be all set

Just an interesting observation, the color of your indicators seems to
be reversed. Normally the long term stuff is given a green and the near
term is given a red, but hey, whatever works for you.

Hope this helps.
John
Project MVP

 

[JulieS]

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field with the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within the next
20 working days. The formula should show "Next Month" for tasks that
start between 21 days and 40 working days from today's date. The
formula should show "Future" for any tasks that starts more than 40 days
from today's date.

You can modify the measurements used (20 days, 40 days) as needed --
just remember that the formula adds minutes -- hence the need to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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

 

[JulieS]

Hi Quirky,

You're welcome and thanks for the feedback.

I see you have posted your new question as a new thread and it looks as
though Mike has given you some guidance.

If you have any follow up questions, go ahead and reply to your latest
post and we'll pitch in again

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Thanks Julie,

Sorry for the delay in replying but I have only just found the time to
implement this change! It worked perfectly thank you...

I am happy with this column but would like to add to it. I was
wondering if
you knew how I could build in my defined quality and reporting
tolerance
levels.

For example, I have tolerance levels of 5% over budget and schedule
overrun
and would like these indicated red. Similarly I also have a 10% under
budget
and ahead of schedule reporting criteria. I would like these to be
marked
green. For the remaining tasks I would like these to appear amber to
signify
that they are same as baseline or within tolerance levels.

Do you or anyone else have any ideas to my problem?

Quirky

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field with
the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within the
next
20 working days. The formula should show "Next Month" for tasks that
start between 21 days and 40 working days from today's date. The
formula should show "Future" for any tasks that starts more than 40
days
from today's date.

You can modify the measurements used (20 days, 40 days) as needed --
just remember that the formula adds minutes -- hence the need to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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

I am trying to Flag tasks using Traffic light system to highlight
what
Tasks
are about to start (ie Green) what tasks are due to start in 1
Month
(amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to
make
this
happen?

Any suggestions????

 

[JulieS]

Hi Quirky,

Don't apologize for asking questions my friend, that is what we are here
for! And you are definitely *not* computer illiterate -- don't judge
your knowledge of computers by your knowledge level of Project -- it's a
tough one sometimes

To your question:

I often find it easier to break these kind of issues into smaller
pieces. So, if one criteria you need to watch for is cost overruns, we
can calculate the percentage of cost overrun to budget by the following
formula:

[Cost Variance]/[Cost]

Expanding that formula a bit further, you want to see if tasks are 5%
over budget. If the tasks are 5% overbudget, display the word "red", if
less that 5% overbudget, display the word "ok".That formula would read:

IIf([Cost Variance]/[Cost]>0.05, "red", "ok")

You also want to see if the task has a "schedule overrun". You don't
define exactly what criteria you are looking for but Finish Variance
shows any difference (positive or negative) of the task Finish as
compared to the Baseline Finish. So the following snippet of formula
may work

[Finish Variance]>0

Combining the two:

IIf([Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red", rest of
formula.....)

Going further:

If the task is 10% under budget and ahead of schedule:
IIf([Cost Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green", "no"

Putting the two together:

IIf[Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red", IIf([Cost
Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green", "amber"))

The problem that I see with your scenario is one of logic. If a task is
wildly overbudget but on schedule (no finish variance) wouldn't that be
a concern? On the other hand, if the costs were okay but the task was
scheduled to be 1 month late, wouldn't that be a concern?

I might suggest actually looking at the two variables separately.
Perhaps one field that tests for cost overruns and displays the desired
flags and another that looks at finish variance and displays the flags.
You could always create yet a third column that would combine the two as
needed as well.

So, a bit long winded but 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

Hi Julie,

Thanks for your help I have read the other comment and I have looked
on the
website. I understand the basic aim but having real trouble figuring
out the
formula which will indicate the differing tolerance levels.

Sorry but I am really computer illiterate please help!

Quirky

Hi Quirky,

You're welcome and thanks for the feedback.

I see you have posted your new question as a new thread and it looks
as
though Mike has given you some guidance.

If you have any follow up questions, go ahead and reply to your
latest
post and we'll pitch in again

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project

Thanks Julie,

Sorry for the delay in replying but I have only just found the time
to
implement this change! It worked perfectly thank you...

I am happy with this column but would like to add to it. I was
wondering if
you knew how I could build in my defined quality and reporting
tolerance
levels.

For example, I have tolerance levels of 5% over budget and schedule
overrun
and would like these indicated red. Similarly I also have a 10%
under
budget
and ahead of schedule reporting criteria. I would like these to be
marked
green. For the remaining tasks I would like these to appear amber
to
signify
that they are same as baseline or within tolerance levels.

Do you or anyone else have any ideas to my problem?

Quirky


:

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field
with
the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within the
next
20 working days. The formula should show "Next Month" for tasks
that
start between 21 days and 40 working days from today's date. The
formula should show "Future" for any tasks that starts more than
40
days
from today's date.

You can modify the measurements used (20 days, 40 days) as
needed --
just remember that the formula adds minutes -- hence the need to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize
fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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


I am trying to Flag tasks using Traffic light system to highlight
what
Tasks
are about to start (ie Green) what tasks are due to start in 1
Month
(amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to
make
this
happen?

Any suggestions????

 

[Quirky]

Thanks Julie,

I think your right with the logic I will break this one down into two fields
with seperate indicators for each. I think it might be possible to do this
off the CPI and SPI I have tried using your previous entry to think up my own
crtieria but getting syntex errors! I did try

I am now trying to say if CPI is less than 95% mark red, if more than 105%
mark amber and if CPI is between these mark green.

I also want the exact same again for SPI put I am presuming I swap the CPI
feild with SPI.

Last Question I promise and thank you Julie you have been a terrific help.

Quirky

Hi Quirky,

Don't apologize for asking questions my friend, that is what we are here
for! And you are definitely *not* computer illiterate -- don't judge
your knowledge of computers by your knowledge level of Project -- it's a
tough one sometimes

To your question:

I often find it easier to break these kind of issues into smaller
pieces. So, if one criteria you need to watch for is cost overruns, we
can calculate the percentage of cost overrun to budget by the following
formula:

[Cost Variance]/[Cost]

Expanding that formula a bit further, you want to see if tasks are 5%
over budget. If the tasks are 5% overbudget, display the word "red", if
less that 5% overbudget, display the word "ok".That formula would read:

IIf([Cost Variance]/[Cost]>0.05, "red", "ok")

You also want to see if the task has a "schedule overrun". You don't
define exactly what criteria you are looking for but Finish Variance
shows any difference (positive or negative) of the task Finish as
compared to the Baseline Finish. So the following snippet of formula
may work

[Finish Variance]>0

Combining the two:

IIf([Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red", rest of
formula.....)

Going further:

If the task is 10% under budget and ahead of schedule:
IIf([Cost Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green", "no"

Putting the two together:

IIf[Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red", IIf([Cost
Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green", "amber"))

The problem that I see with your scenario is one of logic. If a task is
wildly overbudget but on schedule (no finish variance) wouldn't that be
a concern? On the other hand, if the costs were okay but the task was
scheduled to be 1 month late, wouldn't that be a concern?

I might suggest actually looking at the two variables separately.
Perhaps one field that tests for cost overruns and displays the desired
flags and another that looks at finish variance and displays the flags.
You could always create yet a third column that would combine the two as
needed as well.

So, a bit long winded but 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

Hi Julie,

Thanks for your help I have read the other comment and I have looked
on the
website. I understand the basic aim but having real trouble figuring
out the
formula which will indicate the differing tolerance levels.

Sorry but I am really computer illiterate please help!

Quirky

Hi Quirky,

You're welcome and thanks for the feedback.

I see you have posted your new question as a new thread and it looks
as
though Mike has given you some guidance.

If you have any follow up questions, go ahead and reply to your
latest
post and we'll pitch in again

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project


Thanks Julie,

Sorry for the delay in replying but I have only just found the time
to
implement this change! It worked perfectly thank you...

I am happy with this column but would like to add to it. I was
wondering if
you knew how I could build in my defined quality and reporting
tolerance
levels.

For example, I have tolerance levels of 5% over budget and schedule
overrun
and would like these indicated red. Similarly I also have a 10%
under
budget
and ahead of schedule reporting criteria. I would like these to be
marked
green. For the remaining tasks I would like these to appear amber
to
signify
that they are same as baseline or within tolerance levels.

Do you or anyone else have any ideas to my problem?

Quirky


:

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field
with
the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within the
next
20 working days. The formula should show "Next Month" for tasks
that
start between 21 days and 40 working days from today's date. The
formula should show "Future" for any tasks that starts more than
40
days
from today's date.

You can modify the measurements used (20 days, 40 days) as
needed --
just remember that the formula adds minutes -- hence the need to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize
fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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


I am trying to Flag tasks using Traffic light system to highlight
what
Tasks
are about to start (ie Green) what tasks are due to start in 1
Month
(amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to
make
this
happen?

Any suggestions????

 

[JulieS]

Hi Quirky,

You're welcome Quirky and again, never apologize for asking questions --
that's how we all learn.

Why don't you post the formula you have tried and then we can narrow
down why you are seeing the error. That may help more than me just
posting another formula.

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

Thanks Julie,

I think your right with the logic I will break this one down into two
fields
with seperate indicators for each. I think it might be possible to do
this
off the CPI and SPI I have tried using your previous entry to think up
my own
crtieria but getting syntex errors! I did try

I am now trying to say if CPI is less than 95% mark red, if more than
105%
mark amber and if CPI is between these mark green.

I also want the exact same again for SPI put I am presuming I swap the
CPI
feild with SPI.

Last Question I promise and thank you Julie you have been a terrific
help.

Quirky

Hi Quirky,

Don't apologize for asking questions my friend, that is what we are
here
for! And you are definitely *not* computer illiterate -- don't judge
your knowledge of computers by your knowledge level of Project --
it's a
tough one sometimes

To your question:

I often find it easier to break these kind of issues into smaller
pieces. So, if one criteria you need to watch for is cost overruns,
we
can calculate the percentage of cost overrun to budget by the
following
formula:

[Cost Variance]/[Cost]

Expanding that formula a bit further, you want to see if tasks are 5%
over budget. If the tasks are 5% overbudget, display the word "red",
if
less that 5% overbudget, display the word "ok".That formula would
read:

IIf([Cost Variance]/[Cost]>0.05, "red", "ok")

You also want to see if the task has a "schedule overrun". You don't
define exactly what criteria you are looking for but Finish Variance
shows any difference (positive or negative) of the task Finish as
compared to the Baseline Finish. So the following snippet of formula
may work

[Finish Variance]>0

Combining the two:

IIf([Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red", rest
of
formula.....)

Going further:

If the task is 10% under budget and ahead of schedule:
IIf([Cost Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green",
"no"

Putting the two together:

IIf[Cost Variance]/[Cost]>0.05 and [Finish Variance]>0, "red",
IIf([Cost
Variance]/[Cost]<=-0.1 and [Finish Variance]<=0, "green", "amber"))

The problem that I see with your scenario is one of logic. If a task
is
wildly overbudget but on schedule (no finish variance) wouldn't that
be
a concern? On the other hand, if the costs were okay but the task
was
scheduled to be 1 month late, wouldn't that be a concern?

I might suggest actually looking at the two variables separately.
Perhaps one field that tests for cost overruns and displays the
desired
flags and another that looks at finish variance and displays the
flags.
You could always create yet a third column that would combine the two
as
needed as well.

So, a bit long winded but 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

Hi Julie,

Thanks for your help I have read the other comment and I have
looked
on the
website. I understand the basic aim but having real trouble
figuring
out the
formula which will indicate the differing tolerance levels.

Sorry but I am really computer illiterate please help!

Quirky

:

Hi Quirky,

You're welcome and thanks for the feedback.

I see you have posted your new question as a new thread and it
looks
as
though Mike has given you some guidance.

If you have any follow up questions, go ahead and reply to your
latest
post and we'll pitch in again

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project


Thanks Julie,

Sorry for the delay in replying but I have only just found the
time
to
implement this change! It worked perfectly thank you...

I am happy with this column but would like to add to it. I was
wondering if
you knew how I could build in my defined quality and reporting
tolerance
levels.

For example, I have tolerance levels of 5% over budget and
schedule
overrun
and would like these indicated red. Similarly I also have a 10%
under
budget
and ahead of schedule reporting criteria. I would like these to
be
marked
green. For the remaining tasks I would like these to appear
amber
to
signify
that they are same as baseline or within tolerance levels.

Do you or anyone else have any ideas to my problem?

Quirky


:

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field
with
the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within
the
next
20 working days. The formula should show "Next Month" for
tasks
that
start between 21 days and 40 working days from today's date.
The
formula should show "Future" for any tasks that starts more
than
40
days
from today's date.

You can modify the measurements used (20 days, 40 days) as
needed --
just remember that the formula adds minutes -- hence the need
to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize
fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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


I am trying to Flag tasks using Traffic light system to
highlight
what
Tasks
are about to start (ie Green) what tasks are due to start in
1
Month
(amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed
to
make
this
happen?

Any suggestions????

 

[JulieS]

Hi Quirky,

Happy Christmas and a Merry New Year to you as well!

It sounds as though you have come up with a possible answer to the
custom graphics field. I would make one suggestion though, instead of
copying and pasting the values from the CPI and SPI fields, in the text
field, just make a formula that refers to the CPI or SPI fields. For
example:
[CPI]

The formula above will also shows whatever value is in the CPI field and
will update as CPI Changes. When you copied and pasted, you were only
grabbing the value at that moment, and if CPI changes you will have to
copy and paste again.

Adding the formula may also solve the problem in your second paragraph.
If, not gives us a bit more detail -- have you tracked the milestones,
what is your status date, etc.

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

Hi Julie,

Happy Christmas and new year!

I have found a way of achieving my aim but I think it is a bit of a
shortcut. I have inserted two new texted fields called Cost Tolerance
and
Scehdule tolerance I have then taken the CPI and SPI scores and pasted
these
in. I then set the graphical indicators to show them inside and
outside my
levels. I think I have cheated and not sure if they will update
automatically
but I think this will be okay.

One problem I have found though is that all my milestones in my
project are
shown as 0 for both the CPI and SPI scores even though they started
and
finished at the required time? Only a little problem but if you know
of a way
this could be rectified I would be happy to hear from you

All the Best

Quirky

Hi Quirky,

You're welcome and thanks for the feedback.

I see you have posted your new question as a new thread and it looks
as
though Mike has given you some guidance.

If you have any follow up questions, go ahead and reply to your
latest
post and we'll pitch in again

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project

Thanks Julie,

Sorry for the delay in replying but I have only just found the time
to
implement this change! It worked perfectly thank you...

I am happy with this column but would like to add to it. I was
wondering if
you knew how I could build in my defined quality and reporting
tolerance
levels.

For example, I have tolerance levels of 5% over budget and schedule
overrun
and would like these indicated red. Similarly I also have a 10%
under
budget
and ahead of schedule reporting criteria. I would like these to be
marked
green. For the remaining tasks I would like these to appear amber
to
signify
that they are same as baseline or within tolerance levels.

Do you or anyone else have any ideas to my problem?

Quirky


:

Hi Quirky,

Insert a Text field (Text1 for example) and customize the field
with
the
following formula:

Switch([Start]<=ProjDateAdd([Current
Date],9600),"Soon",[Start]>ProjDateAdd([Current Date],9600) And
[Start]<=ProjDateAdd([Current Date],19200),"Next
Month",[Start]>ProjDateAdd([Current Date],19200),"Future")

The formula should show "Soon" for any task that starts within the
next
20 working days. The formula should show "Next Month" for tasks
that
start between 21 days and 40 working days from today's date. The
formula should show "Future" for any tasks that starts more than
40
days
from today's date.

You can modify the measurements used (20 days, 40 days) as
needed --
just remember that the formula adds minutes -- hence the need to
multiply all values by 480 (8 hours * 60 minutes)

Then select the Graphical Indicators button in the Customize
fields
dialog box. Use the following tests:
Row 1
Text1 equals Soon - green indicator
Text1 equals Next month - yellow indicator
Text1 equals Future - red indicator


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


I am trying to Flag tasks using Traffic light system to highlight
what
Tasks
are about to start (ie Green) what tasks are due to start in 1
Month
(amber)
and What Tasks are due to start in 3 Months (Red).

However finding it hard to understand what formula is needed to
make
this
happen?

Any suggestions????