In this example, we want to apply three different colors, depending on how much the original date varies from the current date:
- Green if the variance is less than 3 days
- Yellow if the variance is between 3 and 10 days
- Red if the variance is greater than 10 days
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.