Create a List of Business Days in Excel

I just came back from training in Omaha Nebraska, where Dick Kusleika and I met some of the smartest Excel analysts you could hope to meet (yes I’m schmoozing for the next training contract). During our one-on-one training sessions, we received this question:

“How do you generate a list of dates that represent only business days.”

.

I’ve had to do this in the past for the purposes of dashboard metrics calculations (revenue per business day, units per business day, etc.). When you do those kinds of calculations, it’s often useful to have a helper table that contains a list of dates that represent business days (dates that are not weekends or holidays).

.

One of the easiest ways to generate a list of business days is to use the WORKDAY or WORKDAY.INTL function. These two functions do pretty much the same thing except the WORKDAY.INTL function (available with Excel 2010 and 2013) allows you to specify when your weekend dates are. Apparently, in some countries, they refuse to believe the weekend is actually Saturday and Sunday.

.

I typically use the WORKDAY function, but for this post, I’ll demo the WORKDAY.INTL.

.

Start by entering a date in a cell (I typically use the last date of the previous year). Then in the cell below, begin your formula. Here, you can see the first argument of the WORKDAY.INTL function a start date. This is the date you want to calculate from. In this case, we use the data in B3.

.

The next argument is the [days] argument. I’ve always interpreted this argument as the increment number of days after the start date. I typically use 1 here to specify the next business day after my start date.

.

The next argument in the WORKDAY.INTL function is [weekend]. This is where you specify what days make up your weekend. In my case, it’s Saturday and Sunday. If you’re in the United States, you can get away with ignoring this altogether, as the default is Saturday and Sunday. Better yet, just use the WORDAY function and you won’t even see this argument.

.

The [holidays] argument allows you to pass a range of dates that represent the holidays in your organization. Simply reference the range of dates here.

.

Here’s the function fully built out. Note that you’ll need to apply absolute reference ($ signs) to the holidays reference . At this point, you simply copy or drag the formula down for however many business dates you need.

.

And look at that – an overbearing manager’s dream. All work days!

Note that weekend and holiday dates do not show up in this list of business days.

.

There you have it. Now you can stop typing that list of work days by hand.

16 thoughts on “Create a List of Business Days in Excel

  1. Jason M

    Good post…I always forget about WORKDAY. My typical approach is to generate all the caldendar days, say in A2:A366 and then apply an Adv. Filter with the criteria:

    =AND(WEEKDAY(A2,2)<6,COUNTIF(holidays,A2)=0)

    and just copy to a new range.

  2. Jared

    As discussed by Mr. Excel and others, using the weekend format of “0000011” where 0=Workday and 1=Non-Workday is helpful for people with unusual weekends.

    =WORKDAY.INTL(B3,1,”0000011″,$E$5:$E$15)

  3. Ian Scott

    Been using this for years for a staff holiday system (number of working days during a period of holiday).
    Expand the Holidays area to three years based on the current year (last year, this year and next year) and make all the dates calculate as necessary (e.g Christmas this year is DATE(YEAR(NOW()),12,25)).
    Even Easter can be calculated (see United States Naval Observatory for the workings) although you will need a user function for this.
    Once set up it needs no maintenance.
    Well, unless the government gives us an extra day to celebrate something – that has to be inserted manually.

  4. Lynda Maynard

    You don’t need remember to use absolute references for your list of holidays if you make it a named range. And if your place of business has a winter break that moves a few days either way depending on what day of the week the actual holidays fall on, make it a dynamic named range. Makes annual updating easy-peasy…

  5. deepProgrammer

    I get the number of working days rather than the list when I tried this function in Excel 2010

  6. Bryan Mendoza

    Thank you very much, this was very helpful to me, helped me saved a lot of time for myself and my friends 🙂 kudos, kind regards and Godspeed! 🙂

  7. David

    Thank you very much for this tip! Regarding your comment, “Apparently, in some countries, they refuse to believe the weekend is actually Saturday and Sunday.”, well, “the weekend” for most Muslim countries is Friday-Saturday; in Israel, pretty much the same; and so on. We live in a global economy with many different faiths and practices.

    http://en.wikipedia.org/wiki/Workweek_and_weekend#Israel

  8. Vicky Martin

    When I drag the formula down, it changes the startdate field, not the days field. It makes all of the rest of the cells 0 January. Any idea what the user error is?

  9. Wiper

    … But what if the firs working day can be saturday or sunday? And when on leave saturday and sunday does not count…
    How to do that whit workday function? Pls

  10. Phil

    Please note for this to work your date cell needs to be above the formula… that way when in this case B3 changes to B4, it’s adjusting the proper date and not adjusting to a blank cell. So have your date in B3 then the formula in B4 referencing B3… then drag it down. This answers Vicky Martin’s question above.

  11. JJ Olinchak

    As a teacher I use the WORKDAY.INTL function to generate a class schedule. So, if a class meets two days a week on Monday and Wednesday, I enter the class start date in the Start_date field and the number 1 in the Days field as described in this article.

    But for the Weekend field I enter the following binary string (enclosed in double quotes) “0101111”. A zero represents a class day (or workday) and 1 a non-class day. The WORKDAY.INTL function assumes the week starts on Monday, so the first 0 in the string represents Monday. I then use autofill to complete the schedule for the duration of the course.

  12. Jim Khayo

    I work on Monday, Tuesday, Thursday and Friday. how can i use Wednesday as holiday all over the months. How will the formula be.

Leave a Reply

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