In my previous post I showed how to calculate all of the UK Bank Holidays for a nominated year,all holidays except the Easter holidays. In this post we will wrap it up by showing how to calculate those holidays.
The holidays covered in the previous post were relatively straightforward, a matter of determining the day of the week for the holiday and adding or subtracting a number of days so as to avoid weekends or to force the date to a Monday.
Easter is a lot more complicated. I won’t go into the details of how to calculate it here, instead I will refer you to to this article in the Independent, and more detail than you will ever need in this Wikipaedia article.
One of the days is easy, Easter Friday is the Friday before Easter Monday, Easter Moday is the Monday after Easter Friday, so we just need to calculate one, the other is easily derived by adding or subtracting 3 days. I decided to base my Power Query calculation on a formula that I have used in Excel for many years to calculate Easter Monday. This formula is
=ROUND(("4/"&target.year)/7+MOD(19*MOD(target.year)-7,30)*.14,0)*7-6+1
This works as Excel dates are stored as the number of days from 1st Jan 1900, today’s date (26th Feb 2016) has a day number of 42426. When you see a date, you are seeing that number formatted in your desired date format. The formula above determines a starting date of 1st April for the nominated year, and does some arithmetic on that underlying date number to get to the date number for Easter Monday. Unfortunately, as far as I am aware Power Query does not provide us with that capability, so we have to roll our own.
As an aside, I make no claims to this formula, it is something I picked up some time ago, because it works.
To facilitate this calculation, I have created three functions, one to calculate the number of days in a given year, another to calculate days to date within a given year. The final function, is a simple function that calls DaysInYear for every year required (1900 to year-1 of the target date in our case) and also calls DayNumberofYear for the year the target date.
DaysInYear Function
I have commented the function, so I hope that it is easy to follow. The approach is to generate a list of dates for the 1st day of each month in the nominated year. This list is then passed to the Power Query Date.DaysInMonth function to generate a list of the number of days in each month in that year, this list is then summed.
(year) =>
let
last.day.year = Date.FromText("31/12/" & year),
//setup a list of month numbers
list.Months = List.Numbers(1,12),
//create a list of first day of each month
list.FirstOfMonths = List.Transform(
list.Months,
each Text.From(year)&"-"&Text.From(_)&"-1"),
//transform to dates
list.FirstAsDates = List.Transform(
list.FirstOfMonths,
each DateTime.Date(DateTime.FromText(_))),
//create list of the days in each month
list.DaysInMonths = List.Transform(
list.FirstAsDates,
each Date.DaysInMonth(_))
in
//sum the list of days in months
List.Sum(list.DaysInMonths)
DayNumberOfYear Function
This function is similar to the DaysInYear function in that it generates a list of days in each month for the year of the nominated date, but it only generates a list for the months before the month nominated date. These days are summed and added to the daynumber of the nominated month.
(date) =>
let
//transform parameter to true date
target.date = DateTime.Date(DateTime.FromText(date)),
//get year and month of target date
year = Date.Year(target.date),
month = Date.Month(target.date),
//build a list of months prior to month of target date
list.Months = List.Numbers(1, month-1),
//generate a list for the first of each month
list.FirstOfMonths = List.Transform(
list.Months,
each Text.From(year)&"-"&Text.From(_)&"-1"),
list.FirstDates = List.Transform(
list.FirstOfMonths,
each DateTime.Date(DateTime.FromText(_))),
list.DaysInMonths = List.Transform(
list.FirstDates,
each Date.DaysInMonth(_))
in
//sum the monthly day values in list and addin the day of this month
if List.Sum(list.DaysInMonths) = null then 0 else List.Sum(list.DaysInMonths)+Date.Day(target.date)
DaysToDate Function
Here we build a list of every year from 1900 until the year before the year of the date we are calculating the days to date for. Each year in this list is passed to our DaysInYear function to generate a list of days for each year. This is summed and added to the days to date for the date we are calculating.
If you notice, when these values are summed I add a test to see whether our list of years includes 1900, adding an extra 1 if it does. This is because Excel erroneously treats 1900 as a leap year, and so will add 366 days for that year. Power Query does not treat 1900 as a leap year, so it calculates 365 days for that year. Although Power Query is handling this correctly, we are emulating that Excel formyula so we add 1 if we include 1900. I know that, as we emulate Excel as in this situation, our list of years will always include 1900, but I have built it so that it is a more generic function.
(target as date) => let //setup the list of years to pass to DaysInYear function - 1900-target year-1 list.Years = List.Numbers(1900, Date.Year(target)-1900), //genereate a list of days in each year list.DaysInYears = List.Transform( list.Years, each DaysInYear(Text.From(_))), //calculate day number in year of target date days.YTD = Number.From(DayNumberOfYear(Date.ToText(DateTime.Date(target)))), //sum them both total.Days = List.Sum(list.DaysInYears) + days.YTD + (if List.Contains(list.Years, 1900) then 1 else 0) in total.Days
Easter Holidays
With these 3 functions, we can now emulate the Excel Easter Monday formula shown above.
Easter.Monday = Date.From(Number.Round(DaysToDate(#date(target.Year,4,1))/7+Number.Mod(19*Number.Mod(target.Year, 19)-7, 30)*.14, 0)*7-6+1),
It looks complex, but each part aligns nicely to the Excel formula. The main addition is that whereas the Excel date, “4/”&target.year, can be directly manipulated arithmetically, in Power Query we have to calculate the number of days, DaysToDate(#date(target.Year,4,1)).
The part that emulates the Excel formula, Number.Round(DaysToDate(#date(target.Year,4,1))/7+Number.Mod(19*Number.Mod(target.Year, 19)-7, 30)*.14, 0)*7-6+1, calculates the number of days from 01/01/1900, whichis passed to the Date.From function to get a date in the format we are familiar with.
As previously mentioned, if we know Easter Monday, Easter Friday is simply derived by subtracting 3 days
Easter.Friday = Date.AddDays(Easter.Monday, -3),
Final Holiday List Generator
The fulll routine that produces a list of the holidays is shown below. The target year is hard-coded here, it would be simple to parameterise it in some way.
let
target.year = 2015,
Jan1 = #date(target.year,1,1),
Jan1DoW = Date.DayOfWeek(Jan1, Day.Monday),
New.Years.Day = if Jan1DoW>4 then Date.AddDays(Jan1,7-Jan1DoW) else Jan1,
Easter.Monday = Date.From(Number.Round(DaysToDate(#date(target.year,4,1))/7+ Number.Mod(19*Number.Mod(target.year, 19)-7, 30)*.14, 0)*7-6+1),
Easter.Friday = Date.AddDays(Easter.Monday, -3),
May1 = #date(target.year,5,1),
May1DoW = Date.DayOfWeek(May1,Day.Monday),
May.Day = Date.AddDays(May1,Number.Mod(7-May1DoW,7)),
May31 = #date(target.year,5,31),
May31DoW = Date.DayOfWeek(May31,Day.Monday),
Spring.Bank.Holiday = Date.AddDays(May31,-May31DoW),
Aug31 = #date(target.year,8,31),
Aug31DoW = Date.DayOfWeek(Aug31, Day.Monday),
Summer.Bank.Holiday = Date.AddDays(Aug31, -Aug31DoW),
Dec25 = #date(target.year,12,25),
Dec25DoW = Date.DayOfWeek(Dec25, Day.Monday),
Christmas.Day = if Dec25DoW>4 then Date.AddDays(Dec25,7-Dec25DoW) else Dec25,
Boxing.Day = Date.AddDays(Christmas.Day, if Dec25DoW=4 then 3 else 1),
Holiday.List = Table.FromList(
{[A="New Year's Day", B=New.Years.Day],
[A="Easter Friday", B=Easter.Friday],
[A="Easter Monday", B=Easter.Monday],
[A="May Day", B=May.Day],
[A="Spring Bank Holiday", B=Spring.Bank.Holiday],
[A="Summer Bank Holiday", B=Summer.Bank.Holiday],
[A="Christmas Day", B=Christmas.Day],
[A="Boxing Day", B=Boxing.Day]},
Record.FieldValues, {"Holiday", Text.From(target.year)})
in
Holiday.List
In Summary
There we have it, a list of all UK Bank Holidays for a given year. This also shows how we can leverage legacy Excel formulas within Power Query.
The Two functions DaysInYear and DayNumberOfYear are very similar in concept and in processing steps, so they could be combined into a single function, or DayNumberOfYear could call a modified version of DaysInYear, all in the cause of removing duplicate code..
Building a list of days in every year since 1900 does seem overkill, but although I scoured the Power Query specs, the Formula LanguageSpecification and the Formula Library Specification, I cannot see any way of getting the number of days for a period within Power Query, hence my functions. In Power Query, dates have a date type, they are not just numbers with a special date display format.
One interesting thing that I found is in the Power Query Date.From function. As I show in the Easter Monday calculation, give Date.From a number and it will return a date. Passing the number 42426, the value that Excel stores for today’s date of 26th Feb 2016, it returns the date 26/02/2016. As Power Query correctly treats 1900 as not a leap year, I was expecting 25/02/2016, and if you count the days directly in Power Query, 26th Feb is indeed a day number of 42425. It seems that Power Query has picked up some of Excel’s legacy errors.
And finally, for those who cannot get enough Excel (isn’t that everyone?), Debra Dalgleish has a comprehensive summary of algorithms, Excel formulae, and UDFs that calculate Easter on her Contextures site.
Bob's BI Ramblings 