How to test a conditional formatting formula

An easy workaround is to test the formula on the worksheet directly.

It should work straight away.

Now let’s try it with a more complicated example.

Here we have some random dates.

We want to be able to highlight dates in any given month.

For the formula, we can use the =TEXT function and “mmm” for the date format.

That gives us the month.

Now we just need to compare each result against the input cell.

The result is a set of boolean values, perfect for CF.

Finally, we need to create the rule.

I’ll copy the formula in I4, then create the new rule.

Notice that I copied the formula that refers to the active cell in the selection, which is B4.

When I save the rule, we have a working conditional format.

I can enter any month and see dates in that month highlighted.