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 assist in calculations like revenue per business 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, 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.
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 you want to increment.
Weekends (optional): By default, the WORKDAY.INTL function excludes Saturdays and Sundays, but this third argument allows you to specify which days to exclude as a weekend day. As you enter the WORKDAYS.INTL function, Excel activates an interactive tooltip from which 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 formula, you tell Excel to start from 12/31/2012 and increment up 1 to give you the next business day after the start date. For the optional arguments, you specify that you 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.