Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Sort One Column or Multiple Columns in Excel
  • How To Filter Data in Excel
  • How to create a Histogram in Excel
  • How to Create Thermometer Chart in Excel
  • How to add Trendline to a chart in Excel

References

  • How to use Excel VLOOKUP Function
  • How to use Excel ROW Function
  • How to get last row in numeric data in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • Basic INDEX MATCH approximate in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation number multiple 100
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number

How to get Holiday Date from Year in Excel

by

This example teaches you how to get the date of a holiday for any year (2019, 2020, etc).

Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date.

1. This is what the spreadsheet looks like. If you enter a year into cell C2, Excel returns all the holidays for that year. Of course, New Year’s Day, Independence Day, Veteran’s Day and Christmas Day are easy.

2. All other holidays can be described in a similar way: the xth day in a month (except Memorial day which is slightly different). Let’s take a look at Thanksgiving Day. If you understand Thanksgiving Day, you understand all holidays. Thanksgiving is celebrated the 4th Thursday in November.

The calendar below helps you understand Thanksgiving Day 2020.

Explanation: DATE(C2,11,1) reduces to 11/1/2020. WEEKDAY(DATE(C2,11,1)) reduces to 1 (Sunday). Now the formula reduces to 11/1/2020 + 21 + CHOOSE(1,4,3,2,1,0,6,5) = 11/1/2020 + 21 + 4 = 11/26/2020. We needed the 4 extra days because it takes 4 days until the first Thursday in November. From there, it takes another 21 days (3 weeks) until the 4rd Thursday in November. It doesn’t matter on which day November 1 falls, the CHOOSE function correctly adds the number of days until the first Thursday in November (notice the pattern in the list of values). From there, it always takes another 21 days until the 4rd Thursday in November. Therefore, this formula works for every year.

3. Let’s take a look at Martin Luther King Jr. Day. This formula is almost the same. Martin Luther King Jr. Day is celebrated the 3rd Monday in January. The first DATE function reduces to the first of January this time. The base position (0) in the list of values for the CHOOSE function is located at the second spot now (we are looking for a Monday)

The calendar below helps you understand Martin Luther King Jr. Day 2020.

Explanation: DATE(C2,1,1) reduces to 1/1/2020. WEEKDAY(DATE(C2,1,1)) reduces to 4 (Wednesday). Now the formula reduces to 1/1/2020 + 14 + CHOOSE(4,1,0,6,5,4,3,2) = 1/1/2020 + 14 + 5 = 1/20/2020. We needed the 5 extra days because it takes 5 days until the first Monday in January.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • Check multiple cells are equal in Excel
  • Invoice status with nested if in Excel
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Add decimal minutes to time in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Assign points based on late time in Excel
  • Get last working day in month in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Running count group by n size in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel

General

  • Find, Select, Replace and Go To Special in Excel
  • How to generate random number between two numbers in Excel
  • Sum by group in Excel
  • List worksheet index numbers in Excel
  • Automatically fill series of cells in Excel using AutoFill
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning