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:
- Write your formula in a cell
- Open Name Manager
- Create a new name
- Write you formula in the Refer to
- 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
20/09/2024 @ 17:25
Hi There
I am a beginner with excel and succesfully managed to create a dynamic calendar that update itself annually. However, i am struggling to create a formula that can auto update fixed annual holidays and the easter holidays. Please help with the formula
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