U.K. Holidays for WORKDAY() and WORKDAY_DIFF(). (Bonus: Easter!)

W_Vann_Hall · ‎Dec 20, 2018

I’ve created a UK version of my base to calculate holidays as input to WORKDAY() or WORKDAY_DIFF(). (For detailed information, see the post referenced above.) The base takes as input the years for which you wish to calculate holidays and the applicable area (England, Wales, Scotland, Northern Ireland, or the Republic of Ireland). The output is a field called Holiday String containing a ISO-formatted list of governmental holidays for the specified locality, ready to be copy-and-pasted as an attribute to either workday function. (Specifying holidays enables the functions to take both weekend days and holidays into account in their calculations.)

This base requires a much more complicated algorithm than the earlier one, given either Good Friday or Easter Monday is an official holiday in the U.K. To calculate either date, one must first arrive at a date for Easter itself — a computation that has remained challenging for hundreds of years. Having absolutely no idea how the calculation works, I merely implemented the algorithm given in the New Scientist of March 30, 1961; my understanding is it works fine for future dates, but fails on historical dates earlier than 1905[ish]. (If you want to extract the Easter-calculation code for use elsewhere, you’ll need fields {Y} — which is simply the numeric value of {Years Desired} — {EMonth}, and {EDay}.)

If you thought the U.S. version of this base was lightly QAed — and I should mention I found and corrected a bug in {Calculations::HolidayByYear} earlier today, so early adopters will want to update their code; fortunately, it won’t cause problems until 2023 — this one was pretty much only given a lick and a promise before sending it out into the world.* So far, it’s handled everything I’ve tossed at it — but, then, I said that about the other base. If any infelicities should appear, please post them as a reply so they can be corrected going forward.

* On my behalf, though, it’s not as if it’s all that easy a base to quality check. For example, here’s the forumla for EDay:

IF(
    {Years Desired},
    MOD((MOD((19*MOD(Y,19)+INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+
    13)/25)+15),30)+MOD(((2*MOD(INT(Y/100),4))+(2*INT(MOD(Y,100)/4))-
    MOD((19*MOD(Y,19)+INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+
    13)/25)+15),30)-MOD(MOD(Y,100),4) +32),7)-(7*INT((MOD(Y,19)+
    (11*MOD((19*MOD(Y,19)+INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+
    13)/25)+15),30))+(19*MOD(((2*MOD(INT(Y/100),4))+(2*INT(MOD(Y,100)/4))-
    MOD((19*MOD(Y,19)+INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+
    13)/25)+15),30)-MOD(MOD(Y,100),4)+32),7)))/433))+(33*INT((MOD((19*MOD(Y,19)+
    INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+13)/25)+15),30)+
    MOD(((2*MOD(INT(Y/100),4))+(2*INT(MOD(Y,100)/4))-MOD((19*MOD(Y,19)+
    INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+13)/25)+15),30)-
    MOD(MOD(Y,100),4)+32),7)-(7*INT((MOD(Y,19)+(11*MOD((19*MOD(Y,19)+
    INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+13)/25)+15),30))+
    (19*MOD(((2*MOD(INT(Y/100),4))+(2*INT(MOD(Y,100)/4))-MOD((19*MOD(Y,19)+
    INT(Y/100)-INT(INT(Y/100)/4)-INT(((8*INT(Y/100))+13)/25)+15),30)-
    MOD(MOD(Y,100),4) +32),7)))/433)) + 90)/25)) + 19),32),
    BLANK()
    )

Jeremy_Oglesby · ‎Dec 21, 2018

That Easter formula makes my head hurt 😖

W_Vann_Hall · ‎Dec 21, 2018

That Easter formula makes my head hurt

Tell me about it…

Fortunately, I didn’t have to understand it; I just had to get it to work.

I found several implementations of either this or an earlier algorithm for Excel or php, but it seemed simpler to reimplement than translate. The New Scientist article contained this description of the formula:

I simply converted it into a list of equations using Airtable formula syntax —

a = MOD(Y,19)
b = INT(Y/100)
c = MOD(Y,100)
d = INT(b/4)
e = MOD(b,4)
g = INT(((8*b) + 13)/25)
h = MOD((19*a) + b - d - g + 15,30)
[...]

— and then started doing substitutions —

a = MOD(Y,19)
b = INT(Y/100)
c = MOD(Y,100)
d = INT(INT(Y/100)/4)
e = MOD(INT(Y/100),4)
g = INT(((8*(INT(Y/100))) + 13)/25)
h = MOD(((19*(MOD(Y,19))) + INT(Y/100) - INT(INT(Y/100)/4) - (INT(((8*(INT(Y/100))) + 13)/25)) + 15),30)

— until I ultimately ended up with that horrifying thing from my earlier post. ({EDay} in the base is equivalent to variable p in the New Scientist algorithm.)

Fortunately, it nearly worked out-of-the-box; once I fixed the inevitable syntax errors, {EDay} immediately began cranking out the correct day for whatever year I supplied. {EMonth} initially returned negative values in the -20s and -30s, but once I’d sorted out my parentheses, it fell into line.

Again, no idea how it works. The New Scientist piece includes a description of the process, but I’ve had a thunderingly painful sinus headache for three days running, so I’m not about to attempt it until I ditch this cold…

Jeremy_Oglesby · ‎Dec 22, 2018

That is truly fascinating!

Good work, @W_Vann_Hall!