Work, Schedule and Cost Slip formulas, help.

[Robert]

Hello,

I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.


Schedule Slip

Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")

Cost Slip

Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")

Work Slip

IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)


Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Dale Howard [MVP]]

Robert --

Although you got some good advice in the Microsoft Project newsgroup, refer
to the following FAQ for a thorough example of how to use a formula and
graphical indicators in an enterprise field:

http://www.projectserverexperts.com/Shared Documents/CreateStoplightIndicators.htm

Hope this helps.

 

[Robert]

Dale,

Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't any
closer to figuring out how to do it.

Robert --

Although you got some good advice in the Microsoft Project newsgroup, refer
to the following FAQ for a thorough example of how to use a formula and
graphical indicators in an enterprise field:

http://www.projectserverexperts.com/Shared Documents/CreateStopligh...

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Hello,

I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.

Schedule Slip

Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")

Cost Slip

Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")

Work Slip

IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)

Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Dale Howard [MVP]]

Robert --

OK, let's start over. What is it you can't figure out how to do? Please
focus on your most important field, formula, and graphical indicators, and
let's see if we can help you. Let us know.

Dale,

Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't any
closer to figuring out how to do it.

Robert --

Although you got some good advice in the Microsoft Project newsgroup,
refer
to the following FAQ for a thorough example of how to use a formula and
graphical indicators in an enterprise field:

http://www.projectserverexperts.com/Shared Documents/CreateStopligh...

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Hello,

I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.

Schedule Slip

Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")

Cost Slip

Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")

Work Slip

IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)

Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Robert]

Okay the most important one is the schedule slip formula. I want the
indicator to be white if there is no baseline, green if its 100% of
baseline work to date, yellow if its > 100%-105% <, red if its >
105%. If you can help me write that so it works, I would be very
grateful.

Robert --

OK, let's start over. What is it you can't figure out how to do? Please
focus on your most important field, formula, and graphical indicators, and
let's see if we can help you. Let us know.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Dale,

Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't any
closer to figuring out how to do it.

Robert --
Although you got some good advice in the Microsoft Project newsgroup,
refer
to the following FAQ for a thorough example of how to use a formula and
graphical indicators in an enterprise field:
http://www.projectserverexperts.com/Shared Documents/CreateStopligh...
Hope this helps.
--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Hello,
I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.
Schedule Slip
Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")
Cost Slip
Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")
Work Slip
IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)
Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Dale Howard [MVP]]

Robert --

I'm not sure I get what you are trying to do. You say "schedule slip" but
then you use the word "work" in your description. Schedule slippage
generally refers to date variance, indicated by the Finish Variance or Start
Variance fields. The system calculates Finish Variance as Finish - Baseline
Finish, measured by default in days. So, for example, if my task was has a
Baseline Finish date of 2/5/07 but its current Finish date is 2/9/07, the
Finish Variance is 4 days, meaning that the task is currently scheduled to
finish 4 days late. If that is what you are looking for, then your
stoplight indicators need to be defined using Day values and not percentages
because there is no way to show the 4 days late as any kind of percentage of
anything.

On the other hand, if you are talking about Work hours going over their
original Baseline Work budget, then you are describing a situation that is
very easy to set up with a formula and graphical indicators. Here's how:

1. Define a custom enterprise task Number field and call it something like
"Percent Work Overbudget."

2. Enter the following formula for the field:

IIf([Baseline Start]=ProjDateValue("NA"), 999999, [Work Variance]/[Baseline
Work])

3. In the Calculation for Task and Group Summary Rows section, select the
"Use formula" option (very important step).

4. Enter the following criteria:

Equals 999999 White
stoplight
Is greater than .05 Red
stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green
stoplight

After you create the custom field with the formula and the graphical
indicators, save and close the Enterprise Global. Exit and relaunch
Microsoft Project Professional to refresh the cached Global file. Open any
enterprise project and immediately press the F9 function key to recalculate
the entire project, including values in your new custom Percent Work
Overbudget field. Insert the Percent Work Overbudget field and examine the
stoplight indicators for each task. Hover your mouse point over any
stoplight indicator to see the underlying data. NOTE: You will not see
graphical indicators for Milestone tasks, since they trigger a Division by
Zero error. That should present no problem since Milestones have not work
assigned to them and cannot go overbuget on Work.

Hope this helps.

Okay the most important one is the schedule slip formula. I want the
indicator to be white if there is no baseline, green if its 100% of
baseline work to date, yellow if its > 100%-105% <, red if its >
105%. If you can help me write that so it works, I would be very
grateful.

Robert --

OK, let's start over. What is it you can't figure out how to do? Please
focus on your most important field, formula, and graphical indicators,
and
let's see if we can help you. Let us know.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Dale,

Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't any
closer to figuring out how to do it.

On Feb 9, 9:52 am, "Dale Howard [MVP]"
<dale(dot)howard(at)msprojectexperts(dot)com> wrote:
Robert --

Although you got some good advice in the Microsoft Project newsgroup,
refer
to the following FAQ for a thorough example of how to use a formula
and
graphical indicators in an enterprise field:

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.

Schedule Slip

Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")

Cost Slip

Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")

Work Slip

IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)

Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Robert]

Thanks alot for taking the time to reply Dale. This will help me
alot. It's what I am looking for.

Robert --

I'm not sure I get what you are trying to do. You say "schedule slip" but
then you use the word "work" in your description. Schedule slippage
generally refers to date variance, indicated by the Finish Variance or Start
Variance fields. The system calculates Finish Variance as Finish - Baseline
Finish, measured by default in days. So, for example, if my task was has a
Baseline Finish date of 2/5/07 but its current Finish date is 2/9/07, the
Finish Variance is 4 days, meaning that the task is currently scheduled to
finish 4 days late. If that is what you are looking for, then your
stoplight indicators need to be defined using Day values and not percentages
because there is no way to show the 4 days late as any kind of percentage of
anything.

On the other hand, if you are talking about Work hours going over their
original Baseline Work budget, then you are describing a situation that is
very easy to set up with a formula and graphical indicators. Here's how:

1. Define a custom enterprise task Number field and call it something like
"Percent Work Overbudget."

2. Enter the following formula for the field:

IIf([Baseline Start]=ProjDateValue("NA"), 999999, [Work Variance]/[Baseline
Work])

3. In the Calculation for Task and Group Summary Rows section, select the
"Use formula" option (very important step).

4. Enter the following criteria:

Equals 999999 White
stoplight
Is greater than .05 Red
stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green
stoplight

After you create the custom field with the formula and the graphical
indicators, save and close the Enterprise Global. Exit and relaunch
Microsoft Project Professional to refresh the cached Global file. Open any
enterprise project and immediately press the F9 function key to recalculate
the entire project, including values in your new custom Percent Work
Overbudget field. Insert the Percent Work Overbudget field and examine the
stoplight indicators for each task. Hover your mouse point over any
stoplight indicator to see the underlying data. NOTE: You will not see
graphical indicators for Milestone tasks, since they trigger a Division by
Zero error. That should present no problem since Milestones have not work
assigned to them and cannot go overbuget on Work.

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Okay the most important one is the schedule slip formula. I want the
indicator to be white if there is no baseline, green if its 100% of
baseline work to date, yellow if its > 100%-105% <, red if its >
105%. If you can help me write that so it works, I would be very
grateful.

Robert --
OK, let's start over. What is it you can't figure out how to do? Please
focus on your most important field, formula, and graphical indicators,
and
let's see if we can help you. Let us know.
--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Dale,
Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't any
closer to figuring out how to do it.
On Feb 9, 9:52 am, "Dale Howard [MVP]"
<dale(dot)howard(at)msprojectexperts(dot)com> wrote:
Robert --
Although you got some good advice in the Microsoft Project newsgroup,
refer
to the following FAQ for a thorough example of how to use a formula
and
graphical indicators in an enterprise field:
http://www.projectserverexperts.com/Shared Documents/CreateStopligh...
Hope this helps.
--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Hello,
I'm trying to setup formulas for work, schedule and cost slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.
Schedule Slip
Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")
Cost Slip
Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")
Work Slip
IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)
Can someone please rewrite these or show me a better way to do it?
I'm looking to figure out how to have it show red if for example if
the finish date is 120% or more of the baseline finish. Thanks for
any help.

 

[Dale Howard [MVP]]

Robert --

You are more than welcome, my friend! I believe you can use my answer to
create the other two fields you need. Hope this helps.

Thanks alot for taking the time to reply Dale. This will help me
alot. It's what I am looking for.

Robert --

I'm not sure I get what you are trying to do. You say "schedule slip"
but
then you use the word "work" in your description. Schedule slippage
generally refers to date variance, indicated by the Finish Variance or
Start
Variance fields. The system calculates Finish Variance as Finish -
Baseline
Finish, measured by default in days. So, for example, if my task was has
a
Baseline Finish date of 2/5/07 but its current Finish date is 2/9/07, the
Finish Variance is 4 days, meaning that the task is currently scheduled
to
finish 4 days late. If that is what you are looking for, then your
stoplight indicators need to be defined using Day values and not
percentages
because there is no way to show the 4 days late as any kind of percentage
of
anything.

On the other hand, if you are talking about Work hours going over their
original Baseline Work budget, then you are describing a situation that
is
very easy to set up with a formula and graphical indicators. Here's how:

1. Define a custom enterprise task Number field and call it something
like
"Percent Work Overbudget."

2. Enter the following formula for the field:

IIf([Baseline Start]=ProjDateValue("NA"), 999999, [Work
Variance]/[Baseline
Work])

3. In the Calculation for Task and Group Summary Rows section, select
the
"Use formula" option (very important step).

4. Enter the following criteria:

Equals 999999 White
stoplight
Is greater than .05 Red
stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green
stoplight

After you create the custom field with the formula and the graphical
indicators, save and close the Enterprise Global. Exit and relaunch
Microsoft Project Professional to refresh the cached Global file. Open
any
enterprise project and immediately press the F9 function key to
recalculate
the entire project, including values in your new custom Percent Work
Overbudget field. Insert the Percent Work Overbudget field and examine
the
stoplight indicators for each task. Hover your mouse point over any
stoplight indicator to see the underlying data. NOTE: You will not see
graphical indicators for Milestone tasks, since they trigger a Division
by
Zero error. That should present no problem since Milestones have not
work
assigned to them and cannot go overbuget on Work.

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Okay the most important one is the schedule slip formula. I want the
indicator to be white if there is no baseline, green if its 100% of
baseline work to date, yellow if its > 100%-105% <, red if its >
105%. If you can help me write that so it works, I would be very
grateful.

On Feb 9, 12:29 pm, "Dale Howard [MVP]"
<dale(dot)howard(at)msprojectexperts(dot)com> wrote:
Robert --

OK, let's start over. What is it you can't figure out how to do?
Please
focus on your most important field, formula, and graphical indicators,
and
let's see if we can help you. Let us know.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

Thanks for the link, but I found the tutorial lacking for what I was
trying to produce. It is a good guide, but I need to figure out how
to do more advanced things and after reading the tutorial I wasn't
any
closer to figuring out how to do it.

On Feb 9, 9:52 am, "Dale Howard [MVP]"
<dale(dot)howard(at)msprojectexperts(dot)com> wrote:
Robert --

Although you got some good advice in the Microsoft Project
newsgroup,
refer
to the following FAQ for a thorough example of how to use a formula
and
graphical indicators in an enterprise field:

Hope this helps.

--
Dale A. Howard [MVP]
Enterprise Project
Trainer/Consultanthttp://www.msprojectexperts.comhttp://www.projectserverexperts.com
"We wrote the book on Project Server"

I'm trying to setup formulas for work, schedule and cost
slippage.
Below is what I have so far, but I know its either not correct or
isn't properly displaying indicators.

Schedule Slip

Switch(Len(CStr([Baseline Finish]))<3,"No
baseline",ProjDateDiff([Baseline Finish],[Finish])/
480>=5,"Later",ProjDateDiff([Baseline Finish],[Finish])/
480>0,"Late",True,"On schedule")

Cost Slip

Switch(Len(CStr([Baseline Finish]))<3,"No baseline",([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more",([Cost]+1)/
([Baseline Cost]+1)>1,"Overbudget",True,"Under budget")

Work Slip

IIf([Remaining Work]*1.2>[Work],1,2) And IIf([Remaining Work]*1<
[Work],3,2) And IIf([Remaining Work]*1.5> [Work],4,2)

Can someone please rewrite these or show me a better way to do
it?
I'm looking to figure out how to have it show red if for example
if
the finish date is 120% or more of the baseline finish. Thanks
for
any help.

 

[Naomi Nissen]

Dale, I was following your formula but my dialog box rejected the last
piece: [baseline work].
Is there something I need to do to enable this? I copied exactly from
your posting above.

thanks, Naomi

 

[Dale Howard [MVP]]

Naomi --

Copy the formula into a Word document first and then click the Show/Hide
button. In the formula, you should probably see a carriage return
character. Remove it, and then copy the new formula into the dialog. It
should work because I copied the formula FROM the dialog to the message I
sent you. Hope this helps.