Workdays and days off

Recently I was asked if Excel can create a three month sequence of dates where the non-working days were Sunday and Monday. It just happened that a couple of months before I experimented with the WORKDAY.INTL Excel function. By the way INTL does stands for International.

The WORKDAY.INTL allows you to create future or past dates that are working days but it also allows you to specify which days are NOT workdays. The days off can be any days of the week and there can be any number of days.

Step by Step Instructions

Move to a new blank worksheet and follow these steps:

• In cell B1 type the date 01/01/2016 and hit Ctrl+1 to format that cell using the second format on the right in the Type box. This format will show the weekday and the full date. In the Sample preview at the top you will be able to see the fully spelled out weekday and month.
• In Cell A2 type 1 and cell A3 type 2. Highlight the two cells and drag the Fill Handle a couple of dozen cells down. These numbers will correspond to the number of future dates we want to create (excluding non-working days).
• We’re ready to go! In B2 type =WORK to display the list of Excel function starting with these letters.
• Hit <Down Arrow> then the Tab key to auto complete the name of the function.
• Hit Ctrl+A to open the WORKDAY.INTL Function Arguments box.
• In the Start_Date box click cell B1 and hit the F4 key twice to show B$1. Hit Tab.
• In the Days box click cell A2.
• In the optional Weekend box enter the parameter matching the weekdays that you are not working. We will see below the various parameter you can use here. For the moment type 2 and hit Enter.
• In the Holidays box, select a range of dates that are holidays. A holiday that falls on a working day will not appear in the series of dates generated by WORKDAY.INTL function.
• Double click the Fill Handle to copy your formula down for each value in column A.

You will note that none of the dates generated by the WORKDAY.INTL include Sunday and Monday. Below you see the completed function box.

About the Weekend argument

The third argument is a bit obscure. How did I know that I had to have 2 there. In short, I looked at the online documentation.

While in the Function box click Help of this function link at the bottom left. A window will open explaining all the possible values of the Weekend parameter. Here you will learn that the value for Sunday and Monday non-workdays is 2.

But how do you specify non-work days other than the options available? For example what if you work part time Wednesday through Friday. Can the function generates all the working dates for that situation. Absolutely!

Specifying Custom Workdays

Near the bottom of the help topic you will find information about coding the workdays vs the non-workdays using a string of 1 and 0 where 1 represents a non-workday and 0 represents a workday. Using that approach, specify seven digit of 1 and 0 where the the first digit is for Monday.

For example the string “1100011” indicates that the workday are the 3rd, 4th and 5th day of the week (starting on Monday!). So the dates generated would be Wednesday, Thursday and Friday.

Hope you learned something useful in that post.

Daniel from ComboProjects