Formula Question

[Marc]

I have limited experience is creating formula's in MS Project. The only one I
use is a Performance Ratio Formula which is IIf([% Work Complete]=100,[Actual
Work]/[Baseline Work],0)... in any event, i have been trying to create three
new formulas which 'should' be easy but I just can't figure it out. Here is
what I want:

Create a new field with a formula that will show me if the task is estimated
to begin (Baseline Start) sometime this week (Monday through Friday).

Create a new field with a formula that will show me if the task is estimated
to finish (Baseline Finish) sometime this week (Monday through Friday).

Some sort of formula that will show me which tasks need to be updated. For
example, if '% Complete' is '0' and the Start Date is less than today's date.

Any guidance or assistance will be greatly appreciated!

 

[JackD]

I think you can work these out if you try.
First you need to test if there is a baseline for the task
You can do that by a number of methods. I show a couple here:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html
Then you need to figure out when now is. To do that you can use the now()
or the date() functions.
Next you need to figure out what the boundries of the week are. You can use
the Weekday() function to determine which day is the current day and adjust
the boundries. So you would be comparing your baseline start to see if it is

Date()-Weekday(date())

AND
<Date()+(7-Weekday(date()))

Obviously you need to test this to make sure I did the math right and see
how you want to handle weekends and you may need to work in integer values
instead of decimal values (see this post:
http://zo-d.com/blog/archives/programming/vba-integer-division-and-mod.html
for details on doing that).

From there it should be simple to construct a nested iif statement. Try it.
If you still have problems, post what you have and I'll help you out from
there.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

I have limited experience is creating formula's in MS Project. The only one I
use is a Performance Ratio Formula which is IIf([% Work Complete]=100,[Actual
Work]/[Baseline Work],0)... in any event, i have been trying to create three
new formulas which 'should' be easy but I just can't figure it out. Here is
what I want:

Create a new field with a formula that will show me if the task is estimated
to begin (Baseline Start) sometime this week (Monday through Friday).

Create a new field with a formula that will show me if the task is estimated
to finish (Baseline Finish) sometime this week (Monday through Friday).

Some sort of formula that will show me which tasks need to be updated. For
example, if '% Complete' is '0' and the Start Date is less than today's date.

Any guidance or assistance will be greatly appreciated!

 

[Marc]

Got it! Thank you, Jack.

Now I am trying to figure out the next piece of what I am trying to do, but
I am lost. How do I change the color of the text? Can you do that? Is
there an example formula I can look at?

To make my question clearer: Here is one formula that I got off this site
that I am now using: IIf([%
Complete]=100,"Complete",IIf((ProjDateDiff(datevalue([Finish]),Datevalue([Current
Date]),[Project Calendar])/480)>4,"Late by 5 or
More",IIf((ProjDateDiff(datevalue([Finish]),Datevalue([Current
Date]),[Project Calendar])/480)>0,"Late by 1-4","Not Late")))

In cases where the value yields either "Late by 5 or More" and "Late by
1-4", how can I change those tasks to, say, red? Is that possible?

Thanks!

Marc

I think you can work these out if you try.
First you need to test if there is a baseline for the task
You can do that by a number of methods. I show a couple here:
http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html
Then you need to figure out when now is. To do that you can use the now()
or the date() functions.
Next you need to figure out what the boundries of the week are. You can use
the Weekday() function to determine which day is the current day and adjust
the boundries. So you would be comparing your baseline start to see if it is

Date()-Weekday(date())

AND
<Date()+(7-Weekday(date()))

Obviously you need to test this to make sure I did the math right and see
how you want to handle weekends and you may need to work in integer values
instead of decimal values (see this post:
http://zo-d.com/blog/archives/programming/vba-integer-division-and-mod.html
for details on doing that).

From there it should be simple to construct a nested iif statement. Try it.
If you still have problems, post what you have and I'll help you out from
there.

--
-Jack ... For Microsoft Project information and macro examples visit
http://masamiki.com/project
or http://zo-d.com/blog/index.html
..

I have limited experience is creating formula's in MS Project. The only one I
use is a Performance Ratio Formula which is IIf([% Work Complete]=100,[Actual
Work]/[Baseline Work],0)... in any event, i have been trying to create three
new formulas which 'should' be easy but I just can't figure it out. Here is
what I want:

Create a new field with a formula that will show me if the task is estimated
to begin (Baseline Start) sometime this week (Monday through Friday).

Create a new field with a formula that will show me if the task is estimated
to finish (Baseline Finish) sometime this week (Monday through Friday).

Some sort of formula that will show me which tasks need to be updated. For
example, if '% Complete' is '0' and the Start Date is less than today's date.

Any guidance or assistance will be greatly appreciated!

 

[Steve House [Project MVP]]

FYI - the "estimated to start" and "estimated to finish" dates might be
wildly different from the Baseline dates. Imagine I have three tasks A, B,
& C each of 10 days duration linked FS. Task A is scheduled to begin Monday
5 Sept. That means Task C is estimated to start 03 Oct and finsih 14 Oct.
I'm ready to start work and I save my baseline. Now we do task A but we
find it actually takes 15 days and now finishes a week later, pushing
everything else out a week in the process. NOW Task C is estimated to start
of 10 Oct and finish 21 Oct because the fact that it is linked says that
given the performance of A & B that is the earliest possible date for it to
take place. But task C's BASELINE hasn't changed, it is still showing 03
Oct start and 14 Oct finish. Baselines preserve the original plan but as
the work on the project progresses, the estimated date work will be able to
take place on a given task can diverge from that.