Calculate Public holidays with Excel

Last Updated on 12/05/2024
Reading time: 3 minutes

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

  1. Calculate the Fixed holidays

    Fixed holidays are holiday that occurs on the same date every year

  2. Calculate the Variable Holidays

    Variable holidays occur according to religious days or specific celebrations (like Thanksgiving)

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

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

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.

Easter Day Formula

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

=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

Thanksgiving formula

Download the file

1 Comment

  1. Paul
    04/06/2023 @ 08:49

    Hi there.
    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.
    Thank you
    Paul

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Calculate Public holidays with Excel

Reading time: 3 minutes
Last Updated on 12/05/2024

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

  1. Calculate the Fixed holidays

    Fixed holidays are holiday that occurs on the same date every year

  2. Calculate the Variable Holidays

    Variable holidays occur according to religious days or specific celebrations (like Thanksgiving)

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

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

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.

Easter Day Formula

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

=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

Thanksgiving formula

Download the file

1 Comment

  1. Paul
    04/06/2023 @ 08:49

    Hi there.
    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.
    Thank you
    Paul

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *