I have created a custom column (Text1) that I am using to show the status of each task. The formula I have created for this column is as follows:
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.05),”Yellow”,[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),”Red”,[Finish]>DateSerial(2012,11,3),”Red”)
Everything is working fine with the formula except for the last criteria. What I’m looking to do is mark any task that extends past a given date (release date), with a red status. For some reason, even though I have tasks which extend past 11/3/12, they are not showing as red. Any ideas or guidance would be greatly appreciated.
I tried this in a simple project (12 tasks) and it seemed to work fine. Get a blank Project file and try it this way to test. There may be some attribute set in your LIVE file that is hindering the results. I was using 2010 by the way if that matters.
If one of the criteria are true for the tasks that extend past 11/3/12 I believe the indicator will be set before that final criterion (red if past 11/3/12) is ever evaluated. In other words, the formula will stop when it gets to the first true statement.
To do a quick test, I changed the “red” to “purple” for the last criterion and tested this in a small schedule. What it looks like happens is that the formula only gets to that last criterion when it drops through the gap in the three variance criteria – when finish variance is greater than 5% but less than 10% of project duration.
As I increased the duration values for a task that finished past 11/3/12, I got green, yellow, purple, then finally red.
How is you progress so far ?
The function DateSerial(2012,11,3) will return a date with time 00:00 (serial: 41216) but Finish date will probably have a time value of 17:00 by default (e.g. if the Finish date is 11-03-2012 17:00, then the serial will be 41261.71 including the time serial).