rebuild a traffic light

[remeng]

Hi All,

Well I just moved to a new job and I am having to start from scratch on a traffic light for a new company. Since the link is in the FAQ is dead, can someone help me out with the code? Here is what I have.

Objectives:

1.

​

If field [deadline] is greater than or equal to the finish date = "green" (essentially if the deadline date is the same day as the finish day or after the finish date then the indicator will be green since it isn't past due)


2.

​

if field [Finish] is greater than the [deadline] but no more than 29 days = "yellow" (essentially the traffic light will show yellow if the finish date is 1 - 29 days beyond the scheduled due date.

3.

​

if the [finish] is 30 days or greater than the [deadline] = "red" (essentially if the finish date is over 30 days passed the deadline, the indicator will be red)

Custom Fields:

day to traffic light (Text1)
Traffic Light (Text2)

Standard Fields:

Finish (built in variable) = [Finish]
Deadline (built in variable) = [Deadline]

Indicators:
"green" = green circle
"yellow" = yellow circle
"red" = red circle

Code:

days to traffic light (Text1):

IIf([Deadline]>99999,'No Deadline',DateDiff('d',[Finish],[Deadline]))

Traffic Light (Text2):

Switch([Text1]=([Deadline]>[Finish]),"green",
[Text1]=([Deadline]=[Finish]),"green",
[Text1]=([Deadline]<[Finish]),"yellow",
[Text1]([Deadline]<([Finish]+29)),"yellow",
[Text1]=([Deadline]<([Finish]+30)),"red")

What am I doing wrong?

Thanks,

Mike

 

[JulieInMaine]

I'd use a slightly revised formula for Text1:

IIf([Deadline]=ProjDateValue("NA"),"No Deadline",DateDiff('d',[Finish],[Deadline]))

That being said the problem with writing formula that may calculate negative values in a text field is that text fields don't recognize the difference between positive and negative values. You really need to enter this into a number field. Of course the issue with a number field is you cannot show text.

Here's my suggestion:

In a Number field use the following formula:
IIf([Deadline]=ProjDateValue("NA"),0,DateDiff('d',[Deadline],[Finish]))

If there is no deadline, the field is zero.
If the deadline is before the finish date, the formula result is zero or a negative number.
If the finish date is after the deadline, the formula result is a positive number.

No need for an additional field for the graphical indicators. Write the formula and make sure the math work. The switch the display value to graphical indicators:

Test for Number 1: Is less than or equal to zero - green graphic
Is greater than or equal to 30 - red graphic
Is less than or equal to 29 - yellow graphic

I hope this helps.

 

[remeng]

Hi Julie,

Always a pleasure to work with you. Could you please add your code in a code box? I'm having a little problem following it as it is formatted. I do agree that this should be able to be generated in a single field since I did it before in the old version of this. As I said I can't remember how

Also, do you know if there is a way to add comments to the code so that if I need to return at a later date and change something, I'll be able to have a reference? Thanks,

Mike

 

[remeng]

Oh I was able to find the original code using a website achieve site. Here it is:

Switch( 

[% Complete]=100,"Complete",
(([% Complete]<100) And ([Finish]<Date())),"Overdue",
(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",
[Finish Variance]<=0,"Green",
[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",
[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red"
)

Can this be modified to be triggered off of the Deadline field and also use my triggers instead?

Thanks,

Mike

 

[JulieInMaine]

Hi Mike,

The formula I suggest is:

[tt]IIf([Deadline]=ProjDateValue("NA"),0,DateDiff('d',[Deadline],[Finish]))[/tt]

This is not VBA code of any sort. It's a fairly straight forward formula in a Number field. The graphical indicators are in the Number field that contains the formula.

I'm not sure I understand about modifying the formula (again, not code) you have above. It is a more complex formula testing for whether a task is complete, whether the task has a baseline, whether the task has finish variance, whether the finish variance is less than 1% of the Project duration or greater than 1% of the project duration. Based upon your description, it is not what you said you wanted.

As far as commenting - no, not really. You can add comments in the Notes field for tasks and some folks do that at the Project summary task level for general comments.

 

[remeng]

I'm very confused by your script / code. Let me see if I have it correctly by breaking it down into parts:

Complete Code

IIf([Deadline]=ProjDateValue("NA"),0,DateDiff('d',[Deadline],[Finish]))

first part:

IIf([Deadline]=ProjDateValue("NA"),0

If the [Deadline] is equal to the Project Date were it is NA, return a 0. Is this correct?

Second Part:

DateDiff('d',[Deadline],[Finish]))

Else the difference of the date (d) ???? returns ???? Here is where I am confused.

Also,

What are the graphical equals values for green, yellow, and red? Also, do I need to set a baseline for this? When I enter the Actual Finish date, my Finish date is also changing.

Thanks!

I've been looking at this way to long and my brain is fried...

 

[JulieInMaine]

The first part:

IIf([Deadline] = ProjDateValue("NA"), 0

tests whether a Deadline is applied to the task. The Deadline field is a date field and as such if there is no date, the value shows as NA. If there is no deadline, the formula returns a zero.

If there is a Deadline, the second part of the IIF is called:

DateDiff("d", [Deadline], [Finish])

This part of the formula calculates in days (the "d") the difference between the Deadline date and the Finish date.

As I noted above:

If there is no deadline, the field is zero.
If the deadline is before the finish date, the formula result is zero or a negative number.
If the finish date is after the deadline, the formula result is a positive number.

Based upon your criteria the graphic values are:

1.
If field [deadline] is greater than or equal to the finish date = "green" (essentially if the deadline date is the same day as the finish day or after the finish date then the indicator will be green since it isn't past due)

2.
if field [Finish] is greater than the [deadline] but no more than 29 days = "yellow" (essentially the traffic light will show yellow if the finish date is 1 - 29 days beyond the scheduled due date.

3.
if the [finish] is 30 days or greater than the [deadline] = "red" (essentially if the finish date is over 30 days passed the deadline, the indicator will be red)

Test for Number 1: Is less than or equal to zero - green graphic
Is greater than or equal to 30 - red graphic
Is less than or equal to 29 - yellow graphic

Better?

 

[SkipVought]

Else the difference is returned.

So you either get ZERO or the difference in days from the IIF().

 

[JulieInMaine]

Correct. It is possible the DateDiff Function will return a zero if the deadline and finish date are equal.

 

[remeng]

I did change the no deadline return to -999999999 so that I could set an icon indicating that.

I was also trying the Actual Finish date and I noticed that when I make any changes to it, the Finish date also changes. Isn't this counter to what seems logical? I do have a baseline set too. What am I doing incorrectly?

Thanks!

 

[JulieInMaine]

You need to save a baseline before tracking (adding anything "actual"). Project will calculate the difference between baseline dates (Baseline Start, Baseline Finish) to show how close to the "plan" you are. Project also saves Baseline Work, Baseline Duration, and Baseline cost and calculates variance against those fields as well.

 

[remeng]

Hi Julie,

As I have set the baseline prior to changing / entering a value in either the Actual Start or Actual Finish fields. The Start date or Finish date are changing when I change the Actual fields.

Thanks.

 

[JulieInMaine]

Correct. The Baseline values remain the same but the Start = Actual Start and Finish = Actual Finish. Project updates the start and finish dates to forecast what is happening to the schedule of future tasks. If you started a task early, Project updates the Start and forecasts the finish. If the task is a predecessor to other tasks, those schedules (Start and Finish) are adjusted.

 

[remeng]

So there isn't a way to lock the start and finish, and show the actual start and actual finish as separate dates? What is really the point then of having the two different sets of fields if the A/S and A/F change the original fields?

 

[JulieInMaine]

No, that is not how Project is designed. The reason to show start and finish as well as actual start and finish is, as I explained above, forecasting the schedule of future tasks. I think you are focused on a single task - you need to look at how the fields are used across the entire project. For an inflight project, you want Actual Start and Actual Finish for tasks that are complete, Actual Start and Finish (forecast) for tasks that are incomplete, and Start and Finish for tasks in the future.

 

[remeng]

I guess I just don't see the need for something that will change like that...

 

[JulieInMaine]

I think we're talking past one another. As I said, the Baseline Start and Baseline Finish fields don't change. You save a baseline after you have planned the project but before you start tracking it. Those values do not change and serve as your "plan" to accomplish the project.

Why would you not want to know your planned data - when, how long, how much work, how much cost?

Then as you start updating your plan, why would you not want the schedule of future tasks to change? You planned on a task taking 15 days and you got it done in 10. Wouldn't you want to see that the successor task moved forward in time so you can alert those resources? You may not be able to start it 5 days early - but wouldn't you want that chance?

Most stakeholders are going to want to know, "Are you on track to finish on time?". Without Baselines to tell you when you committed to finish (Baseline Finish) and updating the schedule, you will not be able to answer that question.

 

[remeng]

Sorry for the confusion. I agree. What I am trying to log is when a task actually started or ended versus when it was originally scheduled. Maybe I should just create two new fields that aren't directly linked to anything...

 

[JulieInMaine]

But that is there now.

When a task actually started = Actual start
When it was originally scheduled = Baseline Start

Start Variance is calculated. You are certainly more than welcome to duplicate what Project has out of the box, but you'd need to write the formulas to calculate the difference yourself.

 

[remeng]

Hi Julie,

I just noticed something with the traffic light function. While it works at the task level it doesn't at the project level. Is there a way to add that to the formula?

Thanks,

Mike