Having the stop light formula use the calendar that has been creat

D

Dale

We have established a calendar with the days off and days working and are
trying to have our stop light formula use this calendar in calculating the
delays.

Our current formula is as follows:

ï® IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start], [Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [% Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([% Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project Date1. Is
there any way that we can assure that in making the calculations that the
days off will not be considered?
 
J

JulieS

Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the duration.

calendar Optional; String. The calendar to use when calculating the
duration. If calendar is not specified, the default for the current
resource is the resource calendar, or for the current task, the task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function by
putting the calendar name into the function. For example the beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual Finish],
"your calendar name here")/480


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
 
D

Dale

Julie thanks. This helps but I have a further question. How do I determine
the "name" of my calendar? We are using only one calendar that was created
in the Enterprise Global Template.

JulieS said:
Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the duration.

calendar Optional; String. The calendar to use when calculating the
duration. If calendar is not specified, the default for the current
resource is the resource calendar, or for the current task, the task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function by
putting the calendar name into the function. For example the beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual Finish],
"your calendar name here")/480


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


Dale said:
We have established a calendar with the days off and days working and
are
trying to have our stop light formula use this calendar in calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project Date1.
Is
there any way that we can assure that in making the calculations that
the
days off will not be considered?
 
J

JulieS

Hi Dale,

I'm not terribly familiar with Project Server, so I am just guessing
based upon my use of Project, without server connections.

The name of the calendar in use in the Project can be seen in Project
Pro under Project > Project Information. The list of calendars in the
project file can been seen in Tools > Change Working time.

You may very well be using the "Standard" calendar which has been
modified with the holidays for your organization.

Just one other comment about your formula: you are using the
ProjDateDiff formula to calculate the difference between Finish and
Baseline Finish -- that value already exists in Project in the Finish
Variance field - no need to recalculate it :)

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


Dale said:
Julie thanks. This helps but I have a further question. How do I
determine
the "name" of my calendar? We are using only one calendar that was
created
in the Enterprise Global Template.

JulieS said:
Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the duration.

calendar Optional; String. The calendar to use when calculating the
duration. If calendar is not specified, the default for the current
resource is the resource calendar, or for the current task, the task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function by
putting the calendar name into the function. For example the
beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual
Finish],
"your calendar name here")/480


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


Dale said:
We have established a calendar with the days off and days working
and
are
trying to have our stop light formula use this calendar in
calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project
Date1.
Is
there any way that we can assure that in making the calculations
that
the
days off will not be considered?
 
D

Dale

Julie sorry I didn't write back until today but I was out of town. I put
together this new macro but I get error messages regarding the "standard"
calendar element. Have I typed it incorrectly?

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],[Standard])/480,

IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish],[Standard])/480-([%
Complete]/100*(ProjDateDiff([Start],[Finish],[Standard])/ 480)),
IIf([Enterprise Project Date1]>[Finish],ProjDateDiff([Baseline
Finish],[Enterprise Project Date1],[Standard])/480+(ProjDateDiff([Start],
[Finish])/480)*(1- [% Complete]/100),ProjDateDiff([Baseline
Finish],[Finish],[Standard])/ 480+ ProjDateDiff([Start],[Enterprise Project
Date1],[Standard])/480-([% Complete]/100*
(ProjDateDiff([Start],[Finish],[Standard])/480)))))


JulieS said:
Hi Dale,

I'm not terribly familiar with Project Server, so I am just guessing
based upon my use of Project, without server connections.

The name of the calendar in use in the Project can be seen in Project
Pro under Project > Project Information. The list of calendars in the
project file can been seen in Tools > Change Working time.

You may very well be using the "Standard" calendar which has been
modified with the holidays for your organization.

Just one other comment about your formula: you are using the
ProjDateDiff formula to calculate the difference between Finish and
Baseline Finish -- that value already exists in Project in the Finish
Variance field - no need to recalculate it :)

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


Dale said:
Julie thanks. This helps but I have a further question. How do I
determine
the "name" of my calendar? We are using only one calendar that was
created
in the Enterprise Global Template.

JulieS said:
Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the duration.

calendar Optional; String. The calendar to use when calculating the
duration. If calendar is not specified, the default for the current
resource is the resource calendar, or for the current task, the task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function by
putting the calendar name into the function. For example the
beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual
Finish],
"your calendar name here")/480


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


We have established a calendar with the days off and days working
and
are
trying to have our stop light formula use this calendar in
calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project
Date1.
Is
there any way that we can assure that in making the calculations
that
the
days off will not be considered?
 
J

JulieS

Hi Dale,

The reference to the calendar in the ProjDateDiff function is a string,
so it would be:

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],"Standard")/480,

etc.

Everywhere you have [Standard] it should be "Standard" with the quotes
included.

Also, as I noted earlier when you are using:

ProjDateDiff([Baseline Finish], [Actual Finish])
or
ProjDateDiff{[Baseline Finish], [Finish])

you are calculating the value that already has been calculated - namely
the [Finish Variance]. You could substitute [Finish Variance] for that
entire portion of the 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


Dale said:
Julie sorry I didn't write back until today but I was out of town. I
put
together this new macro but I get error messages regarding the
"standard"
calendar element. Have I typed it incorrectly?

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],[Standard])/480,

IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish],[Standard])/480-([%
Complete]/100*(ProjDateDiff([Start],[Finish],[Standard])/ 480)),
IIf([Enterprise Project Date1]>[Finish],ProjDateDiff([Baseline
Finish],[Enterprise Project
Date1],[Standard])/480+(ProjDateDiff([Start],
[Finish])/480)*(1- [% Complete]/100),ProjDateDiff([Baseline
Finish],[Finish],[Standard])/ 480+ ProjDateDiff([Start],[Enterprise
Project
Date1],[Standard])/480-([% Complete]/100*
(ProjDateDiff([Start],[Finish],[Standard])/480)))))


JulieS said:
Hi Dale,

I'm not terribly familiar with Project Server, so I am just guessing
based upon my use of Project, without server connections.

The name of the calendar in use in the Project can be seen in Project
Pro under Project > Project Information. The list of calendars in
the
project file can been seen in Tools > Change Working time.

You may very well be using the "Standard" calendar which has been
modified with the holidays for your organization.

Just one other comment about your formula: you are using the
ProjDateDiff formula to calculate the difference between Finish and
Baseline Finish -- that value already exists in Project in the Finish
Variance field - no need to recalculate it :)

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


Dale said:
Julie thanks. This helps but I have a further question. How do I
determine
the "name" of my calendar? We are using only one calendar that was
created
in the Enterprise Global Template.

:

Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the
duration.

calendar Optional; String. The calendar to use when calculating
the
duration. If calendar is not specified, the default for the
current
resource is the resource calendar, or for the current task, the
task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function
by
putting the calendar name into the function. For example the
beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual
Finish],
"your calendar name here")/480


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


We have established a calendar with the days off and days
working
and
are
trying to have our stop light formula use this calendar in
calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project
Date1.
Is
there any way that we can assure that in making the calculations
that
the
days off will not be considered?
 
D

Dale

Julie thanks that did it. I appreciate your help

JulieS said:
Hi Dale,

The reference to the calendar in the ProjDateDiff function is a string,
so it would be:

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],"Standard")/480,

etc.

Everywhere you have [Standard] it should be "Standard" with the quotes
included.

Also, as I noted earlier when you are using:

ProjDateDiff([Baseline Finish], [Actual Finish])
or
ProjDateDiff{[Baseline Finish], [Finish])

you are calculating the value that already has been calculated - namely
the [Finish Variance]. You could substitute [Finish Variance] for that
entire portion of the 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


Dale said:
Julie sorry I didn't write back until today but I was out of town. I
put
together this new macro but I get error messages regarding the
"standard"
calendar element. Have I typed it incorrectly?

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],[Standard])/480,

IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish],[Standard])/480-([%
Complete]/100*(ProjDateDiff([Start],[Finish],[Standard])/ 480)),
IIf([Enterprise Project Date1]>[Finish],ProjDateDiff([Baseline
Finish],[Enterprise Project
Date1],[Standard])/480+(ProjDateDiff([Start],
[Finish])/480)*(1- [% Complete]/100),ProjDateDiff([Baseline
Finish],[Finish],[Standard])/ 480+ ProjDateDiff([Start],[Enterprise
Project
Date1],[Standard])/480-([% Complete]/100*
(ProjDateDiff([Start],[Finish],[Standard])/480)))))


JulieS said:
Hi Dale,

I'm not terribly familiar with Project Server, so I am just guessing
based upon my use of Project, without server connections.

The name of the calendar in use in the Project can be seen in Project
Pro under Project > Project Information. The list of calendars in
the
project file can been seen in Tools > Change Working time.

You may very well be using the "Standard" calendar which has been
modified with the holidays for your organization.

Just one other comment about your formula: you are using the
ProjDateDiff formula to calculate the difference between Finish and
Baseline Finish -- that value already exists in Project in the Finish
Variance field - no need to recalculate it :)

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


Julie thanks. This helps but I have a further question. How do I
determine
the "name" of my calendar? We are using only one calendar that was
created
in the Enterprise Global Template.

:

Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of the
duration.

date2 Required; Variant. The date used as the end of the
duration.

calendar Optional; String. The calendar to use when calculating
the
duration. If calendar is not specified, the default for the
current
resource is the resource calendar, or for the current task, the
task
calendar (or the standard calendar if there is no task calendar).

=====================

You can specify the calendar to use in the ProjDateDiff function
by
putting the calendar name into the function. For example the
beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual
Finish],
"your calendar name here")/480


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


We have established a calendar with the days off and days
working
and
are
trying to have our stop light formula use this calendar in
calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([% Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project
Date1.
Is
there any way that we can assure that in making the calculations
that
the
days off will not be considered?
 
J

JulieS

Hi Dale,

You're most welcome and thanks for the feedback.


Julie
Project MVP

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


Dale said:
Julie thanks that did it. I appreciate your help

JulieS said:
Hi Dale,

The reference to the calendar in the ProjDateDiff function is a
string,
so it would be:

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],"Standard")/480,

etc.

Everywhere you have [Standard] it should be "Standard" with the
quotes
included.

Also, as I noted earlier when you are using:

ProjDateDiff([Baseline Finish], [Actual Finish])
or
ProjDateDiff{[Baseline Finish], [Finish])

you are calculating the value that already has been calculated -
namely
the [Finish Variance]. You could substitute [Finish Variance] for
that
entire portion of the 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


Dale said:
Julie sorry I didn't write back until today but I was out of town.
I
put
together this new macro but I get error messages regarding the
"standard"
calendar element. Have I typed it incorrectly?

IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish],[Standard])/480,

IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish],[Standard])/480-([%
Complete]/100*(ProjDateDiff([Start],[Finish],[Standard])/ 480)),
IIf([Enterprise Project Date1]>[Finish],ProjDateDiff([Baseline
Finish],[Enterprise Project
Date1],[Standard])/480+(ProjDateDiff([Start],
[Finish])/480)*(1- [% Complete]/100),ProjDateDiff([Baseline
Finish],[Finish],[Standard])/ 480+ ProjDateDiff([Start],[Enterprise
Project
Date1],[Standard])/480-([% Complete]/100*
(ProjDateDiff([Start],[Finish],[Standard])/480)))))


:

Hi Dale,

I'm not terribly familiar with Project Server, so I am just
guessing
based upon my use of Project, without server connections.

The name of the calendar in use in the Project can be seen in
Project
Pro under Project > Project Information. The list of calendars in
the
project file can been seen in Tools > Change Working time.

You may very well be using the "Standard" calendar which has been
modified with the holidays for your organization.

Just one other comment about your formula: you are using the
ProjDateDiff formula to calculate the difference between Finish
and
Baseline Finish -- that value already exists in Project in the
Finish
Variance field - no need to recalculate it :)

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


Julie thanks. This helps but I have a further question. How do
I
determine
the "name" of my calendar? We are using only one calendar that
was
created
in the Enterprise Global Template.

:

Hi Dale,

From help:
===================
ProjDateDiff( date1, date2, calendar )

date1 Required; Variant. The date used as the beginning of
the
duration.

date2 Required; Variant. The date used as the end of the
duration.

calendar Optional; String. The calendar to use when
calculating
the
duration. If calendar is not specified, the default for the
current
resource is the resource calendar, or for the current task, the
task
calendar (or the standard calendar if there is no task
calendar).

=====================

You can specify the calendar to use in the ProjDateDiff
function
by
putting the calendar name into the function. For example the
beginning
portion of your function would read:

IIf([% Complete]=100, ProjDateDiff( [Baseline Finish], [Actual
Finish],
"your calendar name here")/480


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


We have established a calendar with the days off and days
working
and
are
trying to have our stop light formula use this calendar in
calculating
the
delays.

Our current formula is as follows:

? IIf([% Complete]=100,
ProjDateDiff([Baseline Finish],[Actual Finish])/480,
IIf([Enterprise Project Date1]<[Start],ProjDateDiff([Baseline
Finish],[Finish])/480-([%
Complete]/100*(ProjDateDiff([Start],
[Finish])/
480)),
IIf([Enterprise Project Date1]>[Finish],
ProjDateDiff([Baseline Finish],[Enterprise Project
Date1])/480+(ProjDateDiff([Start], [Finish])/480)*(1- [%
Complete]/100),
ProjDateDiff([Baseline Finish],[Finish])/ 480+
ProjDateDiff([Start],[Enterprise Project Date1])/480-([%
Complete]/100*
(ProjDateDiff([Start],[Finish])/480)))))

we are using "Now() as the calculation for Enterprise Project
Date1.
Is
there any way that we can assure that in making the
calculations
that
the
days off will not be considered?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top