Highlight holidays in Excel by learning simple formulas to calculate fixed and variable public holidays. This guide will help you use Excel formulas to return the date of public holidays in any given year.

Fixed Public Holidays

Fixed public holidays occur on the same date each year, regardless of the day of the week. For example, New Year’s Day and Christmas are celebrated on January 1 and December 25, respectively.

• July 4 – United States – National Day
• July 14 – French – National Day
• June 24 – Quebec’s – Saint-Jean-Baptiste Day
• July 21 – Belgium – National Day
• August 1 – Switzerland – National Day
• October 2 – India – Gandhi Jayanti
• October 12 – Spain – National Day

To calculate these fixed holidays, use the DATE function and change only the year:

=DATE(2021,1,1) → New Year’s Day

=DATE(2021,12,25) → Christmas Day

Variable Public Holidays

Variable holidays depend on specific events that change each year, like Easter. Easter is key for some holidays, as it falls on the first Sunday after the first full moon following March 21.

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

To simplify this complex formula, you can create a custom LAMBDA function in Excel 365:

1. Write your formula in a cell
2. Open Name Manager
3. Create a new name
4. Write you formula in the Refer to
5. In this example, the year is an argument for the LAMBDA function.

Holidays Linked to Easter

• Easter Monday: Easter + 1
• Ascension Day: Easter + 39
• Pentecost: Easter + 49
• Whit Monday: Easter + 50

Other Variable Public Holidays

Here are formulas for other variable holidays in the world

• Thanksgiving (US):
  • 4th Thursday in November
  • =DATE(YEAR(TODAY()),11,CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),11,1)),26,25,24,23,22,28,27))
• Thanksgiving (Canada):
  • 2nd Monday in October
  • =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-11MOD(YEAR(TODAY()),19),30))/7,)7-6+50

Download the file below for the US public holidays in Excel.

Related Articles

• Excel Calendar with Automatic Monthly Data Saving
• Create a weekly calendar with just one formula in Excel
• First day – Last day in Excel
• Calculate Monday Previous Week with Excel
• Calculate Monday in 2 weeks with Excel
• Displaying Days in Letters in Excel
• Write Months in Excel in a Foreign Language
• How to display Months in Letters in Excel?
• Formula First Monday of the month with Excel