This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Show & Tell
- U.K. Holidays for WORKDAY() and WORKDAY_DIFF(). (B...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

4
3073
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 20, 2018 08:36 PM

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()
)
```

Reply

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2018 01:42 PM

That Easter formula makes my head hurt `😖`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 21, 2018 09:59 PM

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

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…

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 22, 2018 07:53 AM

That is truly fascinating!

Good work, @W_Vann_Hall!