# Baseline variance formula

### Question

• Can anyone explain how this formula calculate Red/Green/Yellow? What fields are used for this calculation? Why my baseline duration is 5 days VS new duration is 16 days and it's still yellow?

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")

Thursday, December 08, 2011 7:26 PM

• As Jan notes, Duration is always in working time as defined in the calendar in use in the project.  The ProjDateDiff function also uses the "Standard" calendar unless you have a task calendar applied.  Are you using the Standard calendar as the project calendar?

Julie

Thursday, December 22, 2011 1:11 PM
• Thank Mimi.  If you are using the Standard calendar, then any holidays (non-working time) are not counted in either the total Project Duration nor in any formulas using the ProjDateDiff function.

This is a puzzler.

Julie

Friday, December 23, 2011 3:01 PM

### All replies

• More or less the following logic:

1) If % Complete, then show "Complete"

2) If %Complete < 100 and Finish < Today's Date, "Overdue" - which is a questionable process in my place.  Finish should never be before today's date on an incomplete task.

3) No Baseline if no baseline has been saved.

4) No Finish variance = green

5) If the Finish Variance is less than 10% of the overall project duration, yellow.

6) Finish variance greater than 10% of the overall project duration, red.

Generally speaking, I think 3-6 are reasonable, but I would take issues with #2.

Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
Thursday, December 08, 2011 9:43 PM
• #2 is slightly different from the others in that it highlights a data quality issue, whereas the others all give a RAG status based on the baseline.

I think #2 is a legitimate check, but not in the context of this field. It belongs alongside other data quality checks like:

• tasks with a past start date that are 0% complete
• tasks with a future start date that are more than 0% complete
• tasks with a future end date that are 100% complete

Friday, December 09, 2011 1:16 PM
• Andrew,

For 5 and 6, can you show me the calculation from the above snapshot? When it comes to real case scenario, I still don't understand why my Baseline duration = 5 days and Current duration is 16 days and Baseline status is still YELLOW. Why it just turns RED when Current duration is 17 days?

Thursday, December 15, 2011 3:34 PM
• Hi Mimi,

What is the overall duration of your project?  As Andrew notes, the portion of the formula:

shows yellow if the Finish Variance is less than or equal to 10% of the project duration

Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",

and red if the Finish Variance is greater than 10% of the project duration.

[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red")

I hope this helps.

Julie

Thursday, December 15, 2011 4:13 PM
• Julie,

Project A has 143.28 days duration. Why "Spec drawings" task has finish variance = 18.75 days and it's still yellow? If I follow what you stated, 18.75/143.28= 13%, which more than 10% and it should turn red.

While "FD step B" task has finish variance = 19.38 and it turns red?

Wednesday, December 21, 2011 2:06 PM
• Hi Mimi,

Sorry, you are correct there is something going on which is tough to see without seeing the actual project file.  Is this a consolidated project file?  Normally "Project A" would be the Project Summary task -- line zero -- not line 1.

Julie

Wednesday, December 21, 2011 2:33 PM
• This is not Master Project file. I select not to show project summary task. My Projec starts at ID1. The reason for that is because this schedule will be added to Master file. At Master file, ID0 will not have baseline information, then I can come down to ID1 which is the top level project that has baseline.
Thursday, December 22, 2011 12:46 AM
• Okay, thanks.  This is a somewhat unusual set up but if it suits what you need, fine.  I'm guessing that the picture below is not from the master project file, it is from the subproject, "Project A" - as a stand-alone file?

The formula is in the Project A file, yes?

Thursday, December 22, 2011 2:40 AM
• Project A is a stand-alone file. The formula is in Project A. Does project duration including holidays?
Thursday, December 22, 2011 3:10 AM
• Hi,

Project duration is measures as workig days according to the Project Calendar.

Greetings,

Thursday, December 22, 2011 10:53 AM
• As Jan notes, Duration is always in working time as defined in the calendar in use in the project.  The ProjDateDiff function also uses the "Standard" calendar unless you have a task calendar applied.  Are you using the Standard calendar as the project calendar?

Julie

Thursday, December 22, 2011 1:11 PM
• I use Standard project calendar with holiday exception.
Thursday, December 22, 2011 9:49 PM
• Thank Mimi.  If you are using the Standard calendar, then any holidays (non-working time) are not counted in either the total Project Duration nor in any formulas using the ProjDateDiff function.

This is a puzzler.

Julie

Friday, December 23, 2011 3:01 PM
• Mimi,

If Julie is stumped, you are really in trouble. Is there any way you could send your file to one of us? It would make it a whole lot easier to troubleshoot. Showing a screenshot of your file just isn't enough to go on.

John

Friday, December 23, 2011 4:44 PM
• Can anyone explain how this formula calculate Red/Green/Yellow? What fields are used for this calculation? Why my baseline duration is 5 days VS new duration is 16 days and it's still yellow?

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")

It is a result of the logic of the formula. Just add the Project Summary task to the table and check the duration value. In the test conditions, you're comparing that value to Finish Variance.

Monday, December 26, 2011 12:16 PM