This article will show you the formulae to calculate the public holidays in Excel according to 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 its national day on the same date, year after year.

• For France, there is July 14th (French Revolution)
• The national day of the United States, it's July the 4th
• Quebec, June 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

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

Or

=FLOOR(DAY(MINUTE(Year/38)/2+56)&"/5/"&Year,7)-34

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.

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 are the steps to create your Easter function with the YEAR as an argument.

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

And the result in Excel is

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 other formulae 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

Download the file

You can find all these formulae in this file