Custom Formulas

[ben]

I am trying to create a custom formula that shows the degree (in %) that a
task is late. I am using the following formula to try this:

IIf(([Duration]/[Baseline Duration])>1.1 And [Finish]>[Baseline
Finish],"More than 10% Behind",IIf(([Duration]/[Baseline Duration])>1 And
[Finish]>[Baseline Finish],"Between 1-10% Behind
Schedule",IIf(([Duration]/[Baseline Duration])=1 Or [Finish]=[Baseline
Finish],"On Schedule",IIf(([Duration]/[Baseline Duration])<1 Or
[Finish]<[Baseline Finish],"Ahead of Schedule"))))

I want all successor tasks on a late task to show late as well but am having
troubles getting the output I want. How does MS Project calculate changes in
[finish]/[baseline finish]? Knowing this will help me taylor my algorithm to
what I want.

Thanks,

Ben

 

[John]

I am trying to create a custom formula that shows the degree (in %) that a
task is late. I am using the following formula to try this:

IIf(([Duration]/[Baseline Duration])>1.1 And [Finish]>[Baseline
Finish],"More than 10% Behind",IIf(([Duration]/[Baseline Duration])>1 And
[Finish]>[Baseline Finish],"Between 1-10% Behind
Schedule",IIf(([Duration]/[Baseline Duration])=1 Or [Finish]=[Baseline
Finish],"On Schedule",IIf(([Duration]/[Baseline Duration])<1 Or
[Finish]<[Baseline Finish],"Ahead of Schedule"))))

I want all successor tasks on a late task to show late as well but am having
troubles getting the output I want. How does MS Project calculate changes in
[finish]/[baseline finish]? Knowing this will help me taylor my algorithm to
what I want.

Thanks,

Ben

Ben,
Without trying to decipher your formula, let me simply answer your
question.

First the simple part. Project doesn't "calculate" changes in Baseline
Finish. Baseline fields change only when the user sets or resets the
baseline for the given task, or all tasks. Baseline values are simply a
snapshot of the active field they represent at the point in time that
the baseline was set.

The Finish field is calculated based on the Start field and the Duration
field. The Finish field is also updated to agree with the Actual Finish
field when the latter is set either via user input or by having %
Complete = 100%. A link to the task may also change the finish date, so
there are several things that might change the Finish field.

Hope this helps.
John
Project MVP

 

[ben]

John,

Thanks for the response. I may not have been clear in what I am looking
for. I am familiar with what each of these terms individually means and is
calculated from. However, when you divide baseline finish by finish fields,
you get "1" (only if it is baselined and you are on schedule. If the
duration increased by a day and the finish is pushed back one day, what
happens to that "1"? How does Project divide a date by a date.

I am trying to create a custom formula that shows the degree (in %) that a
task is late. I am using the following formula to try this:

IIf(([Duration]/[Baseline Duration])>1.1 And [Finish]>[Baseline
Finish],"More than 10% Behind",IIf(([Duration]/[Baseline Duration])>1 And
[Finish]>[Baseline Finish],"Between 1-10% Behind
Schedule",IIf(([Duration]/[Baseline Duration])=1 Or [Finish]=[Baseline
Finish],"On Schedule",IIf(([Duration]/[Baseline Duration])<1 Or
[Finish]<[Baseline Finish],"Ahead of Schedule"))))

I want all successor tasks on a late task to show late as well but am having
troubles getting the output I want. How does MS Project calculate changes in
[finish]/[baseline finish]? Knowing this will help me taylor my algorithm to
what I want.

Thanks,

Ben

Ben,
Without trying to decipher your formula, let me simply answer your
question.

First the simple part. Project doesn't "calculate" changes in Baseline
Finish. Baseline fields change only when the user sets or resets the
baseline for the given task, or all tasks. Baseline values are simply a
snapshot of the active field they represent at the point in time that
the baseline was set.

The Finish field is calculated based on the Start field and the Duration
field. The Finish field is also updated to agree with the Actual Finish
field when the latter is set either via user input or by having %
Complete = 100%. A link to the task may also change the finish date, so
there are several things that might change the Finish field.

Hope this helps.
John
Project MVP

 

[John]

John,

Thanks for the response. I may not have been clear in what I am looking
for. I am familiar with what each of these terms individually means and is
calculated from. However, when you divide baseline finish by finish fields,
you get "1" (only if it is baselined and you are on schedule. If the
duration increased by a day and the finish is pushed back one day, what
happens to that "1"? How does Project divide a date by a date.

Ben,
OK, I guess I didn't understand your original question. However now I
don't understand what you expect to get by diving two dates. What are
you expecting?

John
Project MVP

I am trying to create a custom formula that shows the degree (in %) that
a
task is late. I am using the following formula to try this:

IIf(([Duration]/[Baseline Duration])>1.1 And [Finish]>[Baseline
Finish],"More than 10% Behind",IIf(([Duration]/[Baseline Duration])>1 And
[Finish]>[Baseline Finish],"Between 1-10% Behind
Schedule",IIf(([Duration]/[Baseline Duration])=1 Or [Finish]=[Baseline
Finish],"On Schedule",IIf(([Duration]/[Baseline Duration])<1 Or
[Finish]<[Baseline Finish],"Ahead of Schedule"))))

I want all successor tasks on a late task to show late as well but am
having
troubles getting the output I want. How does MS Project calculate
changes in
[finish]/[baseline finish]? Knowing this will help me taylor my
algorithm to
what I want.

Thanks,

Ben

Ben,
Without trying to decipher your formula, let me simply answer your
question.

First the simple part. Project doesn't "calculate" changes in Baseline
Finish. Baseline fields change only when the user sets or resets the
baseline for the given task, or all tasks. Baseline values are simply a
snapshot of the active field they represent at the point in time that
the baseline was set.

The Finish field is calculated based on the Start field and the Duration
field. The Finish field is also updated to agree with the Actual Finish
field when the latter is set either via user input or by having %
Complete = 100%. A link to the task may also change the finish date, so
there are several things that might change the Finish field.

Hope this helps.
John
Project MVP

 

[Crook]

Ben,

Instead of dividing duration by baseline duration, you could try comparing
the finish variance to a percentage of the baseline duration. Ex:
IIF([Finish Variance] <= [Baseline Duration] * 0.1, True, False)

HTH,
Crook

John,

Thanks for the response. I may not have been clear in what I am looking
for. I am familiar with what each of these terms individually means and
is
calculated from. However, when you divide baseline finish by finish
fields,
you get "1" (only if it is baselined and you are on schedule. If the
duration increased by a day and the finish is pushed back one day, what
happens to that "1"? How does Project divide a date by a date.

Ben,
OK, I guess I didn't understand your original question. However now I
don't understand what you expect to get by diving two dates. What are
you expecting?

John
Project MVP

I am trying to create a custom formula that shows the degree (in %)
that
a
task is late. I am using the following formula to try this:

IIf(([Duration]/[Baseline Duration])>1.1 And [Finish]>[Baseline
Finish],"More than 10% Behind",IIf(([Duration]/[Baseline Duration])>1
And
[Finish]>[Baseline Finish],"Between 1-10% Behind
Schedule",IIf(([Duration]/[Baseline Duration])=1 Or
[Finish]=[Baseline
Finish],"On Schedule",IIf(([Duration]/[Baseline Duration])<1 Or
[Finish]<[Baseline Finish],"Ahead of Schedule"))))

I want all successor tasks on a late task to show late as well but am
having
troubles getting the output I want. How does MS Project calculate
changes in
[finish]/[baseline finish]? Knowing this will help me taylor my
algorithm to
what I want.

Thanks,

Ben

Ben,
Without trying to decipher your formula, let me simply answer your
question.

First the simple part. Project doesn't "calculate" changes in Baseline
Finish. Baseline fields change only when the user sets or resets the
baseline for the given task, or all tasks. Baseline values are simply a
snapshot of the active field they represent at the point in time that
the baseline was set.

The Finish field is calculated based on the Start field and the
Duration
field. The Finish field is also updated to agree with the Actual Finish
field when the latter is set either via user input or by having %
Complete = 100%. A link to the task may also change the finish date, so
there are several things that might change the Finish field.

Hope this helps.
John
Project MVP