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 19051ish]. (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()
)