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
Nov 22
Nov 23
Nov 24
Nov 25
Nov 26
Nov 27
Nov 28
and like we said only one of these days is a Thursday so lets format the
date
Thu,Nov 22
Thu,Nov 23
Thu,Nov 24
Thu,Nov 25
Thu,Nov 26
Thu,Nov 27
Thu,Nov 28
With Power Query this is a simple merge and the year is irrelevant
11/22/2020 Sun
11/23/2020 Mon
11/24/2020 Tue
11/25/2020 Wed
11/26/2020 Thu Thanksgiving
11/27/2020 Fri
11/28/2020 Sat
Lets take 2032
11/22/2032 Mon
11/23/2032 Tue
11/24/2032 Wed
11/25/2032 Thu Thanksgiving
11/26/2032 Fri
11/27/2032 Sat
11/28/2032 Sun
For fixed holidays like New Years, it occurs on the 1st but the day of
week varies so we have
1/1 Mon
1/1 Tue
1/1 Wed
1/1 Thu
1/1 Fri
1/1 Sat
1/1 Sun
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
7*H
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.