Holidays date patterns
Ok, everyone has their holiday table with any number of dates.
Some will maintain this every year while others will store up -
say 100 years of holidays to maintain the table only once every
100 years but what if we can avoid all this?
I’ll be using US holidays for this discussion but this can
work for almost any holiday.
Holidays like New Years are on the same date but not the same day of week
while others like Thanksgiving are on the same day of week but
not the same date.
Either way, each holiday occurs on 1 of 7 days every year so what
if our holiday table could handle this?
For example, Thanksgiving is on the 4th Thursday of every November
which means its 3 weeks from the 1st Thursday which is
Nov 1 - Nov 7 therefore Thanksgiving will always be Nov 22 - Nov 28
and only ONE of those days is a Thursday.
How can we use this information to our advantage? Continuing to work
with Thanksgiving we have
and like we said only one of these days is a Thursday so lets format the
With Power Query this is a simple merge and the year is irrelevant
11/26/2020 Thu Thanksgiving
Lets take 2032
11/25/2032 Thu Thanksgiving
For fixed holidays like New Years, it occurs on the 1st but the day of
week varies so we have
We can also add Observed holidays like Fri, 12/31 and Mon, 1/2s
For each holiday we’ll have 7 entries in our table but the advantage
is we only have to maintain it once. If we to compare this with a typical
holiday table with H holidays * Y years, the date pattern would require only
The reason I thought of this is because I was the one who had to create the
100 year holiday table a few years back. Seriously - not kidding.