Microsoft_Excel_2013_logo.svg

Get a List of Holidays in Excel!

Creating a list of holidays in Excel is fairly straight-forward to accomplish with a nesting of the CHOOSE, WEEKDAY, and DATE functions. Using these three functions, you can build formulas in Excel that will automatically tell you when all of the holidays are for a given year and what day of the week each holiday falls on. Some holidays are pretty easy to determine. For example, Halloween occurs on the same day every year. But for the holidays that move every year (holidays like Thanksgiving, which occurs on the 4th Thursday of November), the solution is a little more complicated. At the end of this article, we’ll provide you with your very own Holiday Template, or you can use the information below to build your own, customized list of holidays.Santa Claus Using Laptop

The first step in building a list of holidays in Excel is in understanding how the CHOOSE, WEEKDAY, and DATE functions work. The DATE function simply returns the date (in Excel date format) from a value you specify. The syntax is DATE=(Year,Month,Day). So, all you need to do is specify the year, month, and day of the date you need. WEEKDAY works on a similar premise. It returns a value between 1 and 7 (1= Sunday, 2= Monday, etc.) based on a date that you specify. So, all you need to do is insert a date into the WEEKDAY formula, and it will tell you the day of the week that particular date falls on.

The key function to creating your list of holidays is the CHOOSE function. The way this function works is that you specify a list of options and then tell Excel which option you’d like to pick. The syntax is =CHOOSE(Option_You_Want,Option1,Option2,Option3,…). The first value in the function will be a positive number. So, if you put a “1” in there, the formula will return Option1. If you put a “2”, it will return Option2.

So, if you wanted to calculate the date that Martin Luther King, Jr. Day falls on in a given year, and your year is provided in cell B2, your formula would look like this:
List of Holidays Formula

This formula will determine what day of the week the first of the month falls on, and then add 14 days (since the holiday never falls on the first or second Tuesday of the month), and then adds additional days based on what day of the week the first of the month is. So, if the first of the month is a Sunday, the formula will add 14 to Jan. 1 (bringing you to Jan. 15) and then add 1 to that to get you from Sunday to the third Monday. The result is Jan. 16. You can use this same formula structure to build out your full list of holidays, especially for holidays that fall on a particular day of the month rather than a particular date.

Get Your Free List of Holidays Now!

Download a free template to create your own list of holidays! And don’t forget to sign up for our free email list, where you’ll receive free Excel tips and tricks directly in your inbox and exclusive discounts for our upcoming training classes.

 

Microsoft_Excel_2013_logo.svg

3 Excel Date Functions Everyone Should Know

Knowing how to use a few of the many available Excel date functions is a useful skill if you have to do any sort of time tracking or reporting. Aside from using proper number formatting, it is also important to know how to use a few of the core Excel date functions. Knowing how to use these date functions will enable you to analyze date/time data in increasingly interesting and more complicated ways. If you learn the three Excel date functions outlined below, you will be well on your way to becoming an Excel power user.

1. Get Started with Excel Date Functions TODAY!

The TODAY and NOW Excel date functions are particularly useful if you need a dynamic function to automatically calculate the date and/or time every time you open or refresh a spreadsheet. The only difference between these two functions is that TODAY will simply return today’s date, while NOW will return the current date and time (calculated using the time settings on your computer). To use either of these functions, simply enter =TODAY() or =NOW() into a cell on your spreadsheet. There is no need to put anything inside the brackets of the function.

2. Determine Working Days With NETWORKDAYS

The best Excel date function to use to calculate the number of working days between two dates is NETWORKDAYS. The syntax is =NETWORKDAYS(Start-Date,End-Date,Holidays). You can enter the dates manually into the formula, but they have to be formatted using Excel’s special serial date formatting. It’s much easier to simply put your dates into two different cells and use cell references to pull them into the formula:

Excel Date Functions

Note that the exclusion of holidays from your calculation is optional. Any dates listed in the holidays section of the formula will be excluded from the working days count.

3. Determine a MONTH/DAY/YEAR or HOUR/MINUTE/SECOND

If you’re presented with a date, and you only need to pull out the particular month of that date, the easiest way to do that is with the MONTH Excel date function. If you use =MONTH(Date), Excel will return the numerical value of the month in the date you have indicated, as in the example below.

Excel Date Functions

The DAY/YEAR/HOUR/MINUTE/SECOND functions all work the same way. These functions are useful if you need to break out the data by segments (day, year, etc.) and are particularly helpful in Pivot Table filters when the standard date filters aren’t feasible.

Put Some Training on Your Calendar

This only scratches the surface of what you can do with Excel date functions. There are many more applications that will enable you to perform increasingly complex data analysis with Excel. To get started, signup for our free email newsletter, where you’ll get Excel tips and tricks directly in your inbox. You’ll also get exclusive discounts for our training classes and webinars.