Formula help

[PhilosophersSage]

I inherited a project when the previous person was canned and haven’t touched
MS Project prior to this inheritance. Upper management looks at the stop
light (0 = 100%, 1 = Green, 2 = Yellow, 3 = Red) to see where we are at on a
weekly basis for the prior week. The problem comes is that the conditions are
set up through Friday what was good for last Friday is not for this Friday.
Also it makes short duration tasks red before they even start. I want to set
up the stop light function to display for a cretin week only not current week
so no matter when one opens the file it will still show the snapshot for that
specific week. Also

The current stop light formula is:
IIf([%
Complete]=100,IIf([Flag20]=Yes,IIf(Left([Notes],2)="CR",0,1.5),0),IIf(([%
Complete]=0 And [Start]<[Date3]) Or [Finish]<[Date3],3,IIf(([% Complete]=0
And [Start]<[Date2]) Or [Finish]<[Date2],2,IIf([Finish]>[Date2],1,4))))

Date2 is:
Now()+14

Date3 is
Now()+(7-(Weekday(Now()+1)))

 

[JulieS]

Hello,

The values in Date2 and Date3 are calculating a two week window
automatically. If it does not automatically show the previous two weeks,
press F9 when opening the file to recalculate Date2 and Date3.

Does that solve the problem of the correct "Friday".

To be able to have the RAG status show for a specific week, you'd have to
re-write the formula in another field referencing other Date fields. Short
of manually entering the date and then filling to the entire project list, I
don't see that working well. It may be possible to prompt for a date range
and then feed that information back but it cannot be done with custom
formulas -- it would require programming, likely through VBA.

If you have skills in VBA and need a nudge or two in the correct direction,
you could post detailed information (including what release of Project, the
specific criteria for the RAG status, etc.) to the Microsoft Project
Developer newsgroup. If you don't have VBA skills, you might still post at
the Developer newsgroup and someone may agree to take on the job for you, but
I'd expect to pay for the customization were I asking.

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

Julie

 

[Jack Dahlgren MVP]

If you change Date2 to [Status Date] + 14 and similar for Date3 it should
use the status date for the project which can be manually set to the status
date applicable for the project (ie: if status is reported Friday, then the
status date would be set to that same Friday date. This will remain valid
and static until the project is updated again.

Set the status date by going to the "Project" menu item, select "Project
Information" and set the status date to the correct date.
This should be standard practice for any project schedule anywhere. It is
the same as putting a date on a financial statement. Or a freshness date
stamp on a carton of milk.

-Jack

 

[PhilosophersSage]

Jack,

Thanks! This really helped!

Everyone,
I am a noob to MS Project and it looks like the guy I inherited this project
didn't know much either. I have gone through all the MSP 2003 on line
training from the MS Office website and the help files, but they are not much
help, do you know of a good place to really learn indepth about MSP 2003?

Thanks

If you change Date2 to [Status Date] + 14 and similar for Date3 it should
use the status date for the project which can be manually set to the status
date applicable for the project (ie: if status is reported Friday, then the
status date would be set to that same Friday date. This will remain valid
and static until the project is updated again.

Set the status date by going to the "Project" menu item, select "Project
Information" and set the status date to the correct date.
This should be standard practice for any project schedule anywhere. It is
the same as putting a date on a financial statement. Or a freshness date
stamp on a carton of milk.

-Jack

I inherited a project when the previous person was canned and haven’t
touched
MS Project prior to this inheritance. Upper management looks at the stop
light (0 = 100%, 1 = Green, 2 = Yellow, 3 = Red) to see where we are at on
a
weekly basis for the prior week. The problem comes is that the conditions
are
set up through Friday what was good for last Friday is not for this
Friday.
Also it makes short duration tasks red before they even start. I want to
set
up the stop light function to display for a cretin week only not current
week
so no matter when one opens the file it will still show the snapshot for
that
specific week. Also

The current stop light formula is:
IIf([%
Complete]=100,IIf([Flag20]=Yes,IIf(Left([Notes],2)="CR",0,1.5),0),IIf(([%
Complete]=0 And [Start]<[Date3]) Or [Finish]<[Date3],3,IIf(([% Complete]=0
And [Start]<[Date2]) Or [Finish]<[Date2],2,IIf([Finish]>[Date2],1,4))))

Date2 is:
Now()+14

Date3 is
Now()+(7-(Weekday(Now()+1)))