Formula for end-of-year holiday week

[Retroshift]

Dear Excel wizards,

I am trying to find an Excel non-VBA formula to automatically calculate the end-of-year holiday week based on the available yearly data.

These are the holiday data (including the 25th of December and the 1st and 2nd of January the next year):

Monday 26 - Friday 30 December 2016

Monday 25 - Friday 29 December 2017

Monday 24 - Monday 31 December 2018

Tuesday 24 - Tuesday 31 December 2019

Thursday 24 - Thursday 31 December 2020

Thursday 23 - Friday 31 December 2021

Friday 23 - Friday 30 December 2022

Monday 25 - Friday 29 December 2023

 

[Gerald Higgins]

Well in that case, I don't think it's an Excel question, I think it's a policy question for somebody to decide.
Once we're clear on that, and we get a consistent rule set, then we can build an Excel formula to implement it.

So apart from that uncertainty, it looks like your rule set is something like this

If 31st Dec = Sat, holiday starts Mon 26th Dec OR Fri 23rd Dec
If 31st Dec = Sun, holiday starts Mon 25th Dec
If 31st Dec = Mon, holiday starts Mon 24th Dec
If 31st Dec = Tue, holiday starts Tue 24th Dec
If 31st Dec = Wed, holiday starts Wed 24th Dec
If 31st Dec = Thu, holiday starts Thu 24th Dec
If 31st Dec = Fri, holiday starts Thu 23rd Dec

If we can resolve the uncertainty around Sat 31st Dec in some way, we can build an Excel formula to deal with this.

Over to you . . .

 

[Retroshift]

Hi Gerald and fellow Excel enthusiasts,

There is a decisive condition added which resolves the uncertainty: the total number of public holidays shall be between 17 days (minimum) and 19 days (maximum).
Could you help me create an Excel formula whether to include or exclude the 23th and/or 24th of December each year, based on the data below?

All the holidays:

*This list disregards Easter and Whit Sunday, since these days already fall on a weekend day.
**27th of July is actually 21st of July