Feedback on this formula

[Bob]

Hi

I have this formula that provides an assessment of how earl or late a
task is wrt the baseline.
It displays in a Text field because of the combination of numbers and
text.

Any feedback is welcome.

IIf([Baseline Finish]>50000,"No
Baseline",FORMAT(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))),"0 %")
& IIf(Sgn(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))))=-1,"
Late"," Early"))


Cheers,

Bob

 

[JackD]

The first piece of feedback I'd give is that it is nearly long enough to hit
the character limit for formulas and further work on it may cause
truncation. The second piece of feedback is that:

ProjDateDiff([Start],[Finish]) = [Duration]

and

ProjDateDiff([Baseline Start],[Baseline Finish]) = [Baseline Duration]

so you really don't need to be doing all that projdatediff work.

The third piece of feedback is that:

[Duration] - [Baseline Duration] = [Duration Variance]

So you don't need to do most of the subtraction work that you are doing.

The fourth thing is that just because a task is taking less time than
planned does not mean that it is "early", at least the way I define early -
which is typically in regard to a particular date rather than a duration.

I think that your formula could be simplifed:

First check for the baseline, then use the following:

iif([Duration Variance] > 0, "Late", "Early")

I'd add another level to manage if duration variance = 0, and you could use
a case statement or an nested iif statement for that.

-Jack Dahlgren

 

[Bob]

Hi,

Thanks for your comments. Pointing out the obvious is often the best
thing anyone can do. Much appreciated.
Could not see the forest for all the trees.


Cheers,
Bob

The first piece of feedback I'd give is that it is nearly long enough to hit
the character limit for formulas and further work on it may cause
truncation. The second piece of feedback is that:

ProjDateDiff([Start],[Finish]) = [Duration]

and

ProjDateDiff([Baseline Start],[Baseline Finish]) = [Baseline Duration]

so you really don't need to be doing all that projdatediff work.

The third piece of feedback is that:

[Duration] - [Baseline Duration] = [Duration Variance]

So you don't need to do most of the subtraction work that you are doing.

The fourth thing is that just because a task is taking less time than
planned does not mean that it is "early", at least the way I define early -
which is typically in regard to a particular date rather than a duration.

I think that your formula could be simplifed:

First check for the baseline, then use the following:

iif([Duration Variance] > 0, "Late", "Early")

I'd add another level to manage if duration variance = 0, and you could use
a case statement or an nested iif statement for that.

-Jack Dahlgren

Hi

I have this formula that provides an assessment of how earl or late a
task is wrt the baseline.
It displays in a Text field because of the combination of numbers and
text.

Any feedback is welcome.

IIf([Baseline Finish]>50000,"No
Baseline",FORMAT(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))),"0 %")
& IIf(Sgn(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))))=-1,"
Late"," Early"))


Cheers,

Bob

 

[Bob]

Here is what I was able to produce...

IIf([Baseline Start]>50000,"No BL",FORMAT((([Duration
Variance])/(IIf([Milestone] And [Baseline Duration]=0,1,[Baseline
Duration]))),"0 %") & IIf([Duration Variance]>0," Worse",IIf([Duration
Variance]=0,""," Better")))

Hi,

Thanks for your comments. Pointing out the obvious is often the best
thing anyone can do. Much appreciated.
Could not see the forest for all the trees.


Cheers,
Bob

The first piece of feedback I'd give is that it is nearly long enough to hit
the character limit for formulas and further work on it may cause
truncation. The second piece of feedback is that:

ProjDateDiff([Start],[Finish]) = [Duration]

and

ProjDateDiff([Baseline Start],[Baseline Finish]) = [Baseline Duration]

so you really don't need to be doing all that projdatediff work.

The third piece of feedback is that:

[Duration] - [Baseline Duration] = [Duration Variance]

So you don't need to do most of the subtraction work that you are doing.

The fourth thing is that just because a task is taking less time than
planned does not mean that it is "early", at least the way I define early -
which is typically in regard to a particular date rather than a duration.

I think that your formula could be simplifed:

First check for the baseline, then use the following:

iif([Duration Variance] > 0, "Late", "Early")

I'd add another level to manage if duration variance = 0, and you could use
a case statement or an nested iif statement for that.

-Jack Dahlgren

Hi

I have this formula that provides an assessment of how earl or late a
task is wrt the baseline.
It displays in a Text field because of the combination of numbers and
text.

Any feedback is welcome.

IIf([Baseline Finish]>50000,"No
Baseline",FORMAT(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))),"0 %")
& IIf(Sgn(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))))=-1,"
Late"," Early"))


Cheers,

Bob

 

[JackD]

Glad you worked it out.
I haven't worked it all out, so what is the Format in there for?

-Jack Dahlgren

Here is what I was able to produce...

IIf([Baseline Start]>50000,"No BL",FORMAT((([Duration
Variance])/(IIf([Milestone] And [Baseline Duration]=0,1,[Baseline
Duration]))),"0 %") & IIf([Duration Variance]>0," Worse",IIf([Duration
Variance]=0,""," Better")))

Hi,

Thanks for your comments. Pointing out the obvious is often the best
thing anyone can do. Much appreciated.
Could not see the forest for all the trees.


Cheers,
Bob

The first piece of feedback I'd give is that it is nearly long enough
to hit
the character limit for formulas and further work on it may cause
truncation. The second piece of feedback is that:

ProjDateDiff([Start],[Finish]) = [Duration]

and

ProjDateDiff([Baseline Start],[Baseline Finish]) = [Baseline Duration]

so you really don't need to be doing all that projdatediff work.

The third piece of feedback is that:

[Duration] - [Baseline Duration] = [Duration Variance]

So you don't need to do most of the subtraction work that you are
doing.

The fourth thing is that just because a task is taking less time than
planned does not mean that it is "early", at least the way I define
early -
which is typically in regard to a particular date rather than a
duration.

I think that your formula could be simplifed:

First check for the baseline, then use the following:

iif([Duration Variance] > 0, "Late", "Early")

I'd add another level to manage if duration variance = 0, and you could
use
a case statement or an nested iif statement for that.

-Jack Dahlgren


Hi

I have this formula that provides an assessment of how earl or late a
task is wrt the baseline.
It displays in a Text field because of the combination of numbers and
text.

Any feedback is welcome.

IIf([Baseline Finish]>50000,"No
Baseline",FORMAT(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))),"0
%")
& IIf(Sgn(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline
Finish])))))=-1,"
Late"," Early"))


Cheers,

Bob

 

[Bob]

When I was developing the formula, sometimes would get too many decimal
places displyed in the %.
The fomat gives me the option of adding 0.00 % to make it cleaner if I
want.

Many thanks.

Bob

Glad you worked it out.
I haven't worked it all out, so what is the Format in there for?

-Jack Dahlgren

Here is what I was able to produce...

IIf([Baseline Start]>50000,"No BL",FORMAT((([Duration
Variance])/(IIf([Milestone] And [Baseline Duration]=0,1,[Baseline
Duration]))),"0 %") & IIf([Duration Variance]>0," Worse",IIf([Duration
Variance]=0,""," Better")))

Hi,

Thanks for your comments. Pointing out the obvious is often the best
thing anyone can do. Much appreciated.
Could not see the forest for all the trees.


Cheers,
Bob

JackD wrote:
The first piece of feedback I'd give is that it is nearly long enough
to hit
the character limit for formulas and further work on it may cause
truncation. The second piece of feedback is that:

ProjDateDiff([Start],[Finish]) = [Duration]

and

ProjDateDiff([Baseline Start],[Baseline Finish]) = [Baseline Duration]

so you really don't need to be doing all that projdatediff work.

The third piece of feedback is that:

[Duration] - [Baseline Duration] = [Duration Variance]

So you don't need to do most of the subtraction work that you are
doing.

The fourth thing is that just because a task is taking less time than
planned does not mean that it is "early", at least the way I define
early -
which is typically in regard to a particular date rather than a
duration.

I think that your formula could be simplifed:

First check for the baseline, then use the following:

iif([Duration Variance] > 0, "Late", "Early")

I'd add another level to manage if duration variance = 0, and you could
use
a case statement or an nested iif statement for that.

-Jack Dahlgren


Hi

I have this formula that provides an assessment of how earl or late a
task is wrt the baseline.
It displays in a Text field because of the combination of numbers and
text.

Any feedback is welcome.

IIf([Baseline Finish]>50000,"No
Baseline",FORMAT(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline Finish])))),"0
%")
& IIf(Sgn(((ProjDateDiff([Baseline Start],[Baseline
Finish])-ProjDateDiff([Start],[Finish]))/(IIf([Milestone] And
ProjDateDiff([Baseline Start],[Baseline
Finish])=0,1,ProjDateDiff([Baseline Start],[Baseline
Finish])))))=-1,"
Late"," Early"))


Cheers,

Bob