Date

Public holidays formulas

Reading Time: 2 minutes

This article will show you the formulae to calculate the public holidays according to a year.

Fixed holidays

Fixed holidays it's where the holiday occurs on the same date every year irrespective of the weekday it falls on.

There are fixed public holidays which are common to all countries of the world like January 1st. December 25, Christmas Day, is a public holiday in (almost) every country in the world.

Also, each country in the world celebrates their national day on the same date, year after year.

  • For France, there is July the 14th (French revolution)
  • The national day of the United States, it's July the 4th
  • Quebec, June the 24th is Saint-Jean-Baptiste
  • Belgium, July the 21st (National holiday)
  • Switzerland, August 1
  • Spain, October the 12
  • ...

To calculate the fixed holidays, just use the DATE function by just changing the value of the year.

= DATE (2020; 1; 1) => New Year

= DATE (2020; 12; 25) => Christmas

Formulae for fixed public holiday

Variable public holidays

There are also variable holidays, the dates of which change from year to year. This is the case for religious days which always fall on Mondays (Easter) or Thursdays (Pentecost).

Easter day calculation

For calendars that celebrate events of the Catholic religion, the key date is Easter day

The rule of Easter day is the first Sunday following (or falls at the same time as) the day of the first full moon after the spring equinox (March the21).

The formula, very complex, to obtain the Easter day is the following one

= ROUND(DATE(Year,4,MOD(234-11*MOD(Year,19),30))/7,0)* 7-6

Because of its great complexity, if you work with the Microsoft 365 version, it will be in your interest to integrate this formula into a LAMBDA function with the year as the argument.

Easter Day Formula

Create your custom Easter function

The Easter function is very complex but you can simplify its use with a LAMBDA function.

LAMBDA allows you to create your custom function. Here is the steps to create your Easter function with the YEAR as argument.

  1. Write your formula in a cell
  2. Open the Name Manager
  3. Write your formula name and the LAMDBA function as new name
Creation of the EASTER function

And the result in Excel is

Custom EASTER function in action in Excel

Public holidays linked to Easter day

Then, the other religious holidays are obtained by adding to Easter, always the same number of fixed days. We calculate the other variable holidays as follows.

Easter Monday = Easter +1

=EasterDay Formula + 1

Ascension = Easter + 39

=EasterDay Formula + 39

Pentecost = Easter + 49

=EasterDay Formula + 49

Other Public Holidays

Here are the formula to calculate public holidays like Thanksgiving

Thanksgiving (US)

=DATE(YEAR(TODAY()),11,CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),11,1)),26,25,24,23,22,28,27))

Thanksgiving (Canada)

=DATE(YEAR(TODAY()),10,CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),10,1)),9,8,14,13,12,11,10))

Whit Monday

=ROUND(DATE(YEAR(TODAY()),4,MOD(234-11*MOD(YEAR(TODAY()),19),30))/7,)7-6+50

Thanksgiving formula

Download the file

You can find all this formulae in this file

Related posts

NOW & TODAY

Frédéric LE GUEN

The Correct Value for a Week Number

Frédéric LE GUEN

Split Date and Time

Frédéric LE GUEN

Leave a Comment