This article will show you the formulae to calculate the public holidays in Excel according to year.
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
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.
- Write your formula in a cell
- Open the Name Manager
- 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
Download the file
You can find all these formulae in this file
04/06/2023 @ 08:49
Is there any way to highlight certain lines that contains public holidays?
I have a spreadsheet that runs down from which day(colomn A) and day number - 1 to 31(Colomn B). In the next colomns are figures(Colomns C - G). On public holidays, I want to highlight the line in order to see more clear when it's a holiday so people don't add any data into that line.
Hope it makes sense what I want and that you can assist.