It would be much better to list your bank holidays in a single column rather than blocking them out by years as you have done. I have done that to demonstrate one solution. This solution reorganizes your bank holiday list into a single column, and uses conditional formatting to outline the dates for each year. It creates a named formula to identify this list. It and modifies your existing conditional formatting formula to search for the date in that list, and uses the same logic to update the formula that determines whether to show a blank.
I have added a fake bank holiday of 1/9/2014 to illustrate, because you have no bank holidays in the months that you show.
Bookmarks