Fixed holidays

Fixed holidays occur 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 celebrates its National Day on the same date, year after year.

• The National day of the United States, it's July the 4th
• For France, there is July 14th (French Revolution)
• 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, use the DATE function by changing the year's value.

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

= DATE (2021; 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.

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

• Create your custom functions with LAMBDA
• Return Arrays with Excel’s LAMBDA Function

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

=The 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