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]

What should the result be in a year when 31st December falls on a Wednesday ?
Such as 31st Dec 2025, I think.
You seem to have covered off the other 6 days of the week falling on 31st Dec.

 

[JamesCanale]

I can't figure the logic here. Thursday the 23rd has me baffled.

 

[Retroshift]

I will add some additional details on the holidays of the years 2016-2023. Probably the holidays during the year that fall on a weekend day are taken into account in the end-of-year holiday calculations?

 

[Gerald Higgins]

I think trying to understand the logic of why those holidays are the way they are, is maybe not too important from an Excel point of view.
If that's what they are, then so be it.
But what about years in which 31st Dec falls on a Wednesday, like 2025 ?
Or don't you have to worry about such years ?

 

[Retroshift]

Hi Gerald,
Thanks for your reply. I am trying to predict the end-of-year holiday week for the years 2024 and beyond with an Excel formula, based on the data mentioned. That is why I wonder if someone can see or find the logic in the yearly holiday weeks.

 

[Gerald Higgins]

There are only 7 possibilities I think, based on what day of the week 31st December falls on.
You have covered 6 of them, and personally, I don't want to guess what you want to do if 31st December falls on a Wednesday.

But if you are saying that for EVERY year in which 31st of December falls on a MONDAY, then the holidays in that year should be the same as they are in 2018, and so on, that's fine, and we can build a formula to do that.

But we can't cope with Wednesday 31st of December without either you telling us what you want to do, or me guessing.

 

[Retroshift]

Ok. So our starting point would be 31st of December? I have found data from the year 2014 where the 31st of December falls on a Wednesday:

 

[Gerald Higgins]

I found an inconsistency in your input data.

In post #4, both 2016 and 2022 end on a Saturday, which suggests the holidays should be the same in those two years.
But your table says they are not the same:
2016 holiday starts Mon 26th Dec
2022 holiday starts Fri 23rd Dec.

How can we resolve this ?

 

[Retroshift]

I found an inconsistency in your input data.

In post #4, both 2016 and 2022 end on a Saturday, which suggests the holidays should be the same in those two years.
But your table says they are not the same:
2016 holiday starts Mon 26th Dec
2022 holiday starts Fri 23rd Dec.

How can we resolve this ?

Exactly. That's what I was wondering myself too. My guess would be that it is because of an extra holiday in 2022 falling on a weekend day as compared to 2016 (marked in yellow). Maybe they added an extra day at the end of 2022 (i.e. 23rd Dec.) to compensate this day?