Not exactly what was asked, but probably the attached calendary will give some ideas. I created it some 15 or 20 years ago, so it doesn't use any newest features, but it is still working.

On sheet HolidayList all holidays are defined, with start and end dates (in case current holidays, the end date will be empty, and current date is used as effective end date) included. (Of-course those are holidays valid for my country, but you can edit the holiday list. There is a sheet protection applied, but no password is used.) Also are here defined holidays with shortened previous workday (5 working hours instead of 8).

On sheet Holidays, the list of valid holidays for year entered on sheet Calendary is composed based on HolidayList. This list is used to mark all holidays on Calendary sheet colored.

Attached Files

Calendary.xls (98.5 KB, 11 views)