In this post you will learn how to generate a list of business days excluding holidays in Excel.
Generating a list of business days excluding holidays –
When creating dashboards and reports in Excel, it’s often useful to have a helper table that
contains a list of dates that represent business days (that is, dates that are not weekends or
holidays). This kind of a helper table can help assist in calculations like revenue per busi-
ness day, units per business day, and so on.
One of the easiest ways to generate a list of business days is to use the WORKDAY.INTL
function. Start with a spreadsheet that contains the last date of the previous year and
a list of your organization’s holidays. As you can see in Figure below, your list of holidays
should be formatted dates.
In the cell beneath the last date of the previous year, enter this formula:
=WORKDAY.INTL(B3,1,1,$D$4:$D$15)At this point, you can copy the formula down to create as many business days as you need
(see Figure below).
The WORKDAY.INTL function returns a workday date based on the number of days you tell
it to increment. This function has two required arguments and two optional arguments:
Start Date (required) This argument is the date to start from.
Days (required) This argument is the number of days from the start date that you want
returned.
Weekends (optional) By default, the WORKDAY.INTL function excludes Saturdays and
Sundays, but this third argument allows you to specify which weekdays to exclude as a
weekend day. As you enter the WORKDAY.INTL function, Excel displays a menu where you
can select the appropriate weekend code.
Holidays (optional) This argument allows you to give Excel a list of dates to exclude in
addition to the weekend days.
In this example, we are telling Excel to start from 12/31/2012 and then increment up 1 to
give us the next business day after our start date. For our optional arguments, we specify
that we need to exclude Saturdays and Sundays, along with the holidays listed in cells
$D$4:$D$15.
=WORKDAY.INTL(B3,1,1,$D$4:$D$15)Be sure to lock down the range for your list of holidays with absolute references so that it
remains locked as you copy your formula down.