June 21, 2021
Excel Calendar 2010-2030 With Holidays
Identifying holiday dates often appears as challenge in Excel. In this article we list formulas you can use to identify American holidays along with two important days: Black Friday and Cyber Monday.
The U.S. government recognizes 10 federal holidays.
- January 1 (Fixed), New Year’s Day
- January 15–21 (Floating Monday), Birthday of Martin Luther King, Jr.
- February 15–21 (Floating Monday), Washington’s Birthday
- May 25–31 (Floating Monday), Memorial Day
- July 4 (Fixed), Independence Day
- September 1–7 (Floating Monday), Labor Day
- October 8–14 (Floating Monday), Columbus Day
- November 11 (Fixed), Veterans Day
- November 22–28 (Floating Thursday), Thanksgiving Day
- December 25 (Fixed), Christmas Day
On U.S. federal holidays, non-essential federal government offices are closed, stock market trading is usually suspended, and every federal government employee is paid for the holiday.
Following formulas return the name of the holidays or leave an empty cell. Use each formula in it’s own separate column.
| Holiday | Formula |
| New Year Day | =IF(AND(MONTH(A2)=1;DAY(A2)=1); “January 1”; “”) |
| Martin Luther King Jr. Day | =IF(A2=(DATE(YEAR(A2); 1; 1)+14+CHOOSE(WEEKDAY(DATE(YEAR(A2); 1; 1)); 1; 0; 6; 5; 4; 3; 2)); “Martin Luther King Jr. Day”; “”) |
| President Day | =IF(A2=(DATE(YEAR(A2); 2; 1)+14+CHOOSE(WEEKDAY(DATE(YEAR(A2); 2; 1)); 1; 0; 6; 5; 4; 3; 2)); “Washington’s Birthday”; “”) |
| Memorial Day | =IF(A2=(DATE(YEAR(A2);5;31)-CHOOSE(WEEKDAY(DATE(YEAR(A2);5;31));6;0;1;2;3;4;5)); “Memorial Day”; “”) |
| Independence Day | =IF(AND(MONTH(A2)=7; DAY(A2)=4); “Independence Day”; “”) |
| Labor Day | =IF(A2=(DATE(YEAR(A2);9;1)+CHOOSE(WEEKDAY(DATE(YEAR(A2);9;1));1;0;6;5; 4; 3; 2)); “Labor Day”; “”) |
| Columbus Day | =IF(A2=DATE(YEAR(A2);10;1)+7+CHOOSE(WEEKDAY(DATE(YEAR(A2);10;1));1; 0; 6; 5; 4; 3; 2); “Columbus Day”; “”) |
| Veterans Day | =IF(AND(MONTH(A2)=11; DAY(A2)=11); “Veterans Day”; “”) |
| Thanksgiving Day | =IF(A2=(DATE(YEAR(A2); 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(YEAR(A2); 11; 1)); 4; 3; 2; 1; 0; 6; 5)); “Thanksgiving”; “”) |
| Christmas Day | =IF(AND(MONTH(A2)=12; DAY(A2)=25); “Christmas Day”; “”) |
For example, for identifying Martin Luther King Jr. Day we add a column and enter the formula.
For business needs, we are also listing formulas for Black Friday and Cyber Monday.
Black Friday is the day after Thanksgiving Day. Many stores offer highly promoted and open very early (sometimes as early as midnight), or some time on Thanksgiving Day. Black Friday has routinely been the busiest shopping day of the year in the United States since at least 2005.
Cyber Monday similarly offers sales and encourages people to shop online. It occurs on Monday after Thanksgiving.
Dates:
- November 23–29 (Floating Friday), day after Thanksgiving Day
- November 26–December 2 (Floating Friday), Monday after Thanksgiving Day
We use the following formulas.
| Black Friday | =IF(A2=(DATE(YEAR(A2); 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(YEAR(A2); 11; 1));
5; 4; 3; 2; 1; 0; 6)); “Black Friday”; “”) |
| Cyber Monday | =IF(A2=DATE(YEAR(A2);11;1)+21
+CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1));4;3;2;1;0;6;5) +CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1)+21 +CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1));4;3;2;1;0;6;5)); 1; 0; 6; 5; 4; 3; 2); “Cyber Monday”; “”) |