In this example, we will show you how to calculate the holiday date for any year in Excel.
Before we start, let’s focus on 2 points:
- Function CHOOSE (SELECT) returns a value depending on the ordinal position. For example: =CHOOSE(3,»Car»,»Train»,»Boat»,»Plane») returns “Boat”.
- Function WEEKDAY (WEEKDAY) returns a number between 1 (Sunday) and 7 (Saturday) representing the number of the day of the week.
In the figure below you can see what the finished table looks like. If you enter a year in a cell C2, Excel displays all holidays of this year. Of course, New Year, Independence Day, Veterans Day and Christmas are easy to calculate.
All other holidays can be described in this way: the nth day of the month (with the exception of Memorial Day, which is slightly different). Let’s take a look at Thanksgiving 2015. If you understand how to calculate Thanksgiving, then you will understand the rest of the holidays. Thanksgiving Day is celebrated every fourth Thursday in November.
=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5)
=ДАТА(C2;11;1)+21+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;11;1));4;3;2;1;0;6;5)
The calendar below will help you figure out when Thanksgiving will be in 2015:
Explanation:
- Expression DATE(C2;11;1) This is November 1, 2015.
- Expression DAY(DATE(C2;11;1)) returns 1, i.e. Sunday.
- ВЫБOP(1;4;3;2;1;0;6;5) returns 4 (from Sunday to Thursday 4 days).
- We get: 01.11.2015/21/4 + XNUMX + XNUMX = 26.11.2015
Note: We need 4 extra days as there are 4 days missing from the first Thursday in November. Add 21 more days (3 weeks) to this date to get the 4th Thursday in November.
It doesn’t matter what day November 1st falls on. Function CHOOSE (SELECT) will still add the correct number of days until the first Thursday in November. Then the formula will add another 21 days until the 4th Thursday in November. Similarly, this formula works for every year.
Now let’s take a look at Martin Luther King Day, which is celebrated annually on the third Monday in January:
=DATE(C2,1,1)+14+CHOOSE(WEEKDAY(DATE(C2,1,1)),1,0,6,5,4,3,2)
=ДАТА(C2;1;1)+14+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;1;1));1;0;6;5;4;3;2)
This formula is almost identical to the previous one. This time:
- Function DATE (DATE) returns January 1st.
- In function arguments CHOOSE (SELECT) position with value 0 is in second place in the list of values, because we are looking for monday.
- If January 1st falls on a Monday, we get: 14 + CHOICE(2;1;0;6;5;4;3;2) = 14 + 0 = 14 days (or 2 weeks) until the 3rd Monday of January.