In Microsoft Project, a field called Finish Variance shows how many days there are between the Finish and the Baseline Finish fields. Using this data can be helpful when trying to determine if your tasks are finishing on, before, or after their planned finish dates.
If you want to use the Finish Variance field in your OnePager Conditional Formatting Rules to show visually if your tasks are late, you’ll run into an issue: Microsoft Project treats Finish Variance as a string field instead of a number.
This article covers how to create a custom number field with a formula that recreates the calculation for the Finish Variance field. Because this custom field is numeric, it will be available for use by OnePager’s Conditional Formatting Rules.
You will need to have a Project plan with a Start/Finish date and Baseline Start/Baseline Finish dates. These entries are required to allow Microsoft Project to calculate the Finish Variance.
Once we have the appropriate project plan open, we will need to add a new number field to our project. It does not matter which number field you use in this exercise.. Once you have the field added, you will need to right-click and go to Custom Fields and select Formula under Custom Attributes. See below for the formula.
ProjDateDiff([Baseline Finish],[Finish],[Project Calendar])/480
We will be using the ProjDateDiff function, as we will want to avoid counting the non -working days in our formula. Also, because ProjDateDiff stores its results in minutes, we need to divide the results by 480, which is the number of minutes in an eight-hour day. Making sure we have the 480 will allow us to get our results in days.
Once we have plugged in the formula, we will see that we get a similar entry as the Finish Variance but without the “days” text at the end. Having just the number will allow us to go into OnePager and use it within our Conditional Formatting Rules and create data-driven coloring based on the Finish Variance. If you look below, I have placed some conditional formatting rules you can use to get started.
After applying the rules to the chart, you will see the data-driven coloring based on the formula we used for the number field. Having this data-driven color coding allows us to show the status of our tasks through visible representation.