How to Generate a List of Business Days Excluding Holidays in Excel

Business days are the days of the week when most businesses are open and operate normally. Typically, business days are Monday to Friday, excluding public holidays. However, some businesses may have different definitions of business days depending on their industry, location, or preferences.

In Excel, you may want to generate a list of business days for various purposes, such as scheduling, planning, reporting, or analysis. For example, you may want to calculate the number of business days between two dates, or create a calendar that shows only the business days in a given month or year.

There are different ways to generate a list of business days in Excel, but one of the most common and flexible methods is to use the WORKDAY function. The WORKDAY function returns a date that is a specified number of working days before or after a given date, excluding weekends and holidays. You can use the WORKDAY function to create a series of dates that are all business days, and then use other functions or features to format, filter, or manipulate the list as needed.

In this article, we will explain how to use the WORKDAY function to generate a list of business days in Excel, and provide a detailed example with real data. We will also discuss some other approaches that you can use to achieve the same goal.

The WORKDAY function has the following syntax:

=WORKDAY(start_date, days, [holidays])

where:

• start_date is the date from which to start counting the working days. It can be a date value, a cell reference, or a formula that returns a date.
• days is the number of working days before or after the start date. It can be a positive or negative integer, a cell reference, or a formula that returns an integer.
• [holidays] is an optional argument that specifies a range of cells that contain the dates of holidays to exclude from the working days. It can be a single cell, a range of cells, or an array of dates.

The WORKDAY function returns a date value that is the result of adding or subtracting the specified number of working days from the start date, excluding weekends and holidays. If the start date or the result date is not a valid date, the function returns a #VALUE! error. If the holidays argument contains invalid dates or values that are not dates, the function ignores them.

Procedures

To generate a list of business days in Excel using the WORKDAY function, follow these steps:

1. Enter the start date in a cell, such as A2. This will be the first date in the list of business days.
2. Enter the number of working days to add or subtract in another cell, such as B2. This will determine the increment or decrement of the dates in the list. For example, if you want to generate a list of business days for a month, you can enter 1 to add one working day for each date, or -1 to subtract one working day for each date.
3. Enter the dates of holidays in a range of cells, such as C2:C10. This will be the list of holidays to exclude from the working days. You can enter the dates manually, or use a formula or a named range to reference the dates from another source, such as a table or a worksheet.
4. In the cell below the start date, such as A3, enter the WORKDAY function using the cells that contain the start date, the number of days, and the holidays as arguments. For example, =WORKDAY(A2,B2,C2:C10). This will return the next business day after the start date, excluding weekends and holidays.
5. Copy the formula down to fill the rest of the cells in the column, or until you reach the end date that you want. This will generate a list of business days in Excel, based on the criteria that you specified.

Example

Let’s say you want to generate a list of business days for January 2024, excluding the weekends and the following holidays: New Year’s Day (January 1), Martin Luther King Jr. Day (January 15), and Inauguration Day (January 20). You also want to format the dates as “mmm dd, yyyy” and apply a conditional formatting rule to highlight the holidays in red.

Here is how you can do it using the WORKDAY function:

1. Enter the start date of January 1, 2024 in cell A2. Format the cell as “mmm dd, yyyy”.
2. Enter 1 in cell B2. This will add one working day for each date in the list.
3. Enter the dates of the holidays in cells C2:C4. Format the cells as “mmm dd, yyyy”.
4. In cell A3, enter the formula =WORKDAY(A2,B2,C2:C4). This will return January 2, 2024, which is the next business day after January 1, 2024, excluding weekends and holidays.
5. Copy the formula down to fill the rest of the cells in column A, or until you reach January 31, 2024. This will generate a list of business days for January 2024 in Excel.
6. Select the range A2:A23, and go to the Home tab, click Conditional Formatting, and select New Rule. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format, and enter the formula =MATCH(A2,$C$2:$C$4,0). Click Format, and select the Fill tab. Choose a red color, and click OK. Click OK again to apply the conditional formatting rule. This will highlight the cells that contain the holidays in red.

The result should look something like this:

Other Approaches

Besides using the WORKDAY function, there are some other ways to generate a list of business days in Excel, such as:

• Using the WORKDAY.INTL function, which allows you to specify a custom weekend pattern, such as Friday and Saturday, or only Sunday. The syntax of the WORKDAY.INTL function is similar to the WORKDAY function, except that it has an additional argument for the weekend. For example, =WORKDAY.INTL(A2,B2,1,C2:C4) will return the next business day after A2, excluding Sundays and the holidays in C2:C4.
• Using the NETWORKDAYS function, which returns the number of working days between two dates, excluding weekends and holidays. You can use the NETWORKDAYS function to calculate the end date of a list of business days, given the start date and the number of working days. For example, =NETWORKDAYS(A2,A2+B2,C2:C4) will return the end date of a list of B2 working days, starting from A2, excluding weekends and the holidays in C2:C4. You can then use the WORKDAY function to fill the dates in between.
• Using the FILTER function, which filters a range of data based on one or more criteria. You can use the FILTER function to filter a list of dates based on whether they are weekdays or holidays. For example, =FILTER(A2:A32,(WEEKDAY(A2:A32,2)<=5)*(ISNA(MATCH(A2:A32,C2:C4,0)))) will filter the dates in A2:A32, and return only those that are weekdays and not holidays. The WEEKDAY function returns the day of the week for a given date, and the MATCH function returns the position of a value in a range, or an error if not found. The ISNA function returns TRUE if the value is an error, and FALSE otherwise.