Note: conditional formatting rules are evaluated relative to the upper left cell in the selection at the time the rule is created, in this case D5.
Calculating variance in days
The variances in column E are calculated by subtracting the original date from the current date with this formula: The result is the difference in days between the original date and the current date. A positive difference represents a late date (i.e. a “slip” in the schedule). A negative difference indicates the current date is ahead of schedule. This works, because Excel dates are serial numbers. The variance shown in column E is for reference only in this example, and is not used by the conditional formatting rules. However, if you treat column E as a helper column, you could write simpler conditional formatting rules that use the variance directly. For each rule, we calculate a variance by subtracting the original date from the “current” date (as explained above). Then we check the result with a logical expression. When an expression returns TRUE, the conditional formatting is triggered. Because we want three separate colors, each with a logical test, we’ll need three separate conditional formatting rules. The screen below shows how the rules have been configured to apply the green, yellow, and red formatting. Note the first two rules have “stop if true” ticked:
Rules are evaluated in the order shown. Rule 1 tests if the variance is less than 3 days. Rule 2 checks if the variance is less than 10 days. Rule 3 checks if the variance is greater than or equal to 10 days. Both rule 1 and rule 2 have “stop if true” enabled. When either rule returns TRUE, Excel will stop checking additional rules.
Overdue by n days from today
You might want to compare a due date to today’s date. To test if dates are overdue by at least n days from today, you can use a formula like this: This formula will return TRUE only when a date is at least n days in the past. When a date is in the future, the difference will be a negative number, so the rule will never fire. For more information on building formula criteria, see 50+ formula criteria examples.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
