Formulas

[Jason]

Need help. I need to have on sched, behind sched =<10days, and over 10days. I
think I have most of the formula, but can't figure out a piece of it.

IIf((([Task Baselined?]="Baselined") And ([Baseline Finish]<[Finish])),"Task
Behind Schedule",IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]=[Finish])),"Task On Schedule",IIf((([Task Baselined?]="Baselined")
And ([Baseline Finish]>[Finish])),"Over Two Weeks",IIf([Task Baselined?]="No
Baseline","Task Not Baselined","Calculation Error"))))

Any thoughts, Jason

 

[Jonathan Sofer]

You can do a datediff comparison of [Baseline Finish] and [Finish] to see if
it is > than two weeks. The datediff syntax can be found in the formula
screen under the available functions. Also be aware that the datediff
probably excludes weekends if you are using a base calendar and so two weeks
might not be 14 days bur rather 10 days or something else depending on when
the dates land. These are things you will have to verify.

Jonathan

 

[Jason]

Thanks Jonathan. I found the datediff syntax but did not have any luck
immplementing. My knowledge is this area is very limited so any additional
suggestions would be wonderful!
--
JPW

You can do a datediff comparison of [Baseline Finish] and [Finish] to see if
it is > than two weeks. The datediff syntax can be found in the formula
screen under the available functions. Also be aware that the datediff
probably excludes weekends if you are using a base calendar and so two weeks
might not be 14 days bur rather 10 days or something else depending on when
the dates land. These are things you will have to verify.

Jonathan

Need help. I need to have on sched, behind sched =<10days, and over
10days. I
think I have most of the formula, but can't figure out a piece of it.

IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]<[Finish])),"Task
Behind Schedule",IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]=[Finish])),"Task On Schedule",IIf((([Task Baselined?]="Baselined")
And ([Baseline Finish]>[Finish])),"Over Two Weeks",IIf([Task
Baselined?]="No
Baseline","Task Not Baselined","Calculation Error"))))

Any thoughts, Jason

 

[Jonathan Sofer]

Here is a formula that works like you want

IIf([Baseline Finish]>55000,"Task Not Baselined",IIf([Baseline
Finish]=[Finish],"Task On Schedule",IIf(ProjDateDiff([Baseline
Finish],[Finish],[Project Calendar])/[Minutes Per Day]>10,"Over 10 Working
Days Late",IIf([Baseline Finish]<[Finish],"Task Behind
Schedule",IIf([Baseline Finish]>[Finish],"Ahead of Schedule","Calculation
Error")))))

I removed the [Task Baselined?] and replaced with [Baseline Finish]>55000 so
you don't have to have an extra custom field. I also only test for it once
and not ANDing itto each conditional statement.

I am not sure you need the last "Calculation Error" but I left it in there.

Jonathan

Thanks Jonathan. I found the datediff syntax but did not have any luck
immplementing. My knowledge is this area is very limited so any additional
suggestions would be wonderful!
--
JPW

You can do a datediff comparison of [Baseline Finish] and [Finish] to see
if
it is > than two weeks. The datediff syntax can be found in the formula
screen under the available functions. Also be aware that the datediff
probably excludes weekends if you are using a base calendar and so two
weeks
might not be 14 days bur rather 10 days or something else depending on
when
the dates land. These are things you will have to verify.

Jonathan

Need help. I need to have on sched, behind sched =<10days, and over
10days. I
think I have most of the formula, but can't figure out a piece of it.

IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]<[Finish])),"Task
Behind Schedule",IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]=[Finish])),"Task On Schedule",IIf((([Task
Baselined?]="Baselined")
And ([Baseline Finish]>[Finish])),"Over Two Weeks",IIf([Task
Baselined?]="No
Baseline","Task Not Baselined","Calculation Error"))))

Any thoughts, Jason

 

[Jason]

Thanks Jonathon... I wish there was better documentation out there to help
folks like me. Thanks again.
--
JPW

Here is a formula that works like you want

IIf([Baseline Finish]>55000,"Task Not Baselined",IIf([Baseline
Finish]=[Finish],"Task On Schedule",IIf(ProjDateDiff([Baseline
Finish],[Finish],[Project Calendar])/[Minutes Per Day]>10,"Over 10 Working
Days Late",IIf([Baseline Finish]<[Finish],"Task Behind
Schedule",IIf([Baseline Finish]>[Finish],"Ahead of Schedule","Calculation
Error")))))

I removed the [Task Baselined?] and replaced with [Baseline Finish]>55000 so
you don't have to have an extra custom field. I also only test for it once
and not ANDing itto each conditional statement.

I am not sure you need the last "Calculation Error" but I left it in there.

Jonathan

Thanks Jonathan. I found the datediff syntax but did not have any luck
immplementing. My knowledge is this area is very limited so any additional
suggestions would be wonderful!
--
JPW

You can do a datediff comparison of [Baseline Finish] and [Finish] to see
if
it is > than two weeks. The datediff syntax can be found in the formula
screen under the available functions. Also be aware that the datediff
probably excludes weekends if you are using a base calendar and so two
weeks
might not be 14 days bur rather 10 days or something else depending on
when
the dates land. These are things you will have to verify.

Jonathan

Need help. I need to have on sched, behind sched =<10days, and over
10days. I
think I have most of the formula, but can't figure out a piece of it.

IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]<[Finish])),"Task
Behind Schedule",IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]=[Finish])),"Task On Schedule",IIf((([Task
Baselined?]="Baselined")
And ([Baseline Finish]>[Finish])),"Over Two Weeks",IIf([Task
Baselined?]="No
Baseline","Task Not Baselined","Calculation Error"))))

Any thoughts, Jason

 

[JulieS]

Pardon me for bumping in Jason. Fellow MVP, Jack Dahlgren has written
several posts about custom formulas that may help you out. See the URL below
for a link to Jack's blog and the search "custom formulas"

http://zo-d.com/mt/mt32/mt-search.cgi?IncludeBlogs=2&search=custom+formulas

Jack's post at:

http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

deals specifically with the trouble you were having about how to test if
there is a baseline saved or not.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Thanks Jonathon... I wish there was better documentation out there to help
folks like me. Thanks again.
--
JPW

Here is a formula that works like you want

IIf([Baseline Finish]>55000,"Task Not Baselined",IIf([Baseline
Finish]=[Finish],"Task On Schedule",IIf(ProjDateDiff([Baseline
Finish],[Finish],[Project Calendar])/[Minutes Per Day]>10,"Over 10 Working
Days Late",IIf([Baseline Finish]<[Finish],"Task Behind
Schedule",IIf([Baseline Finish]>[Finish],"Ahead of Schedule","Calculation
Error")))))

I removed the [Task Baselined?] and replaced with [Baseline Finish]>55000 so
you don't have to have an extra custom field. I also only test for it once
and not ANDing itto each conditional statement.

I am not sure you need the last "Calculation Error" but I left it in there.

Jonathan

Thanks Jonathan. I found the datediff syntax but did not have any luck
immplementing. My knowledge is this area is very limited so any additional
suggestions would be wonderful!
--
JPW


:

You can do a datediff comparison of [Baseline Finish] and [Finish] to see
if
it is > than two weeks. The datediff syntax can be found in the formula
screen under the available functions. Also be aware that the datediff
probably excludes weekends if you are using a base calendar and so two
weeks
might not be 14 days bur rather 10 days or something else depending on
when
the dates land. These are things you will have to verify.

Jonathan

Need help. I need to have on sched, behind sched =<10days, and over
10days. I
think I have most of the formula, but can't figure out a piece of it.

IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]<[Finish])),"Task
Behind Schedule",IIf((([Task Baselined?]="Baselined") And ([Baseline
Finish]=[Finish])),"Task On Schedule",IIf((([Task
Baselined?]="Baselined")
And ([Baseline Finish]>[Finish])),"Over Two Weeks",IIf([Task
Baselined?]="No
Baseline","Task Not Baselined","Calculation Error"))))

Any thoughts, Jason