Using Statistical Software to Skip Weekends and Holidays

Minitab Blog Editor 29 March, 2012

The other day I was having lunch with a Minitab user. She asked if Minitab Statistical Software could calculate the number of business days between two dates, excluding weekends and holidays. “I’m not sure,” I said. “I’ll look into it."

I am happy to report that Minitab can indeed do this. Which is good, because this would be a pretty disappointing blog post otherwise. It’s actually really easy, you just use the NetWorkdays function in the Minitab calculator. It automatically excludes weekends, and if you list the holidays, it will exclude those as well. I’ll show you with an example.

In column C2 of the worksheet below, I’ve listed the numerous holidays that my colleagues here in Sydney will enjoy in 2012, including the Queen’s Birthday and Labour day (with a “u”). In columns C3 and C4, I’ve also added the start and end dates for a few hypothetical projects.

Worksheet showing holidays and project dates

Calculating the number of business days between the start and end of each hypothetical project is as easy as choosing Calc > Calculator and entering the following formula. You might want to change the Functions drop-down to Date/Time to narrow the choices in the function list. I also like to check Assign as a formula, so that the results column will automatically update whenever I add or change the data in the source columns:

Calc > Calculator

The results of the formula appear in the new column, "Business Days":

Worksheet showing business days for each project

One thing you might notice right away is that Minitab counts both the start and end dates as days. So the project that started on the 16th and ended the next day on the 17th is listed as taking 2 business days. I personally prefer to think of that as taking a single business day. Fortunately, all I have to do is add “- 1” to the end of my formula:

Subtracting 1 from the formula

And voila! The project that started one day and ended the next is now shown as taking only 1 business day:

Business days using corrected formula

For reference, here’s a hypothetical calendar showing the two weeks when the hypothetical projects hypothetically occurred:

Calendar showing projects

Notice that the "Longer Project" took only 3 business days. Minitab excludes the January 26 holiday (Australia Day) that I listed in column C2. And the "Longest Project" took 8 days. Minitab excludes the weekend and Australia Day.

So you see, it's easy to count business days and exclude weekends and as many holidays as you can convince your company or your government to pack into a year. I am currently lobbying my congressman to institute Statistics Appreciation Day as a mandatory holiday. I'll let you know how I make out with that.