Sitemap  · Français  · English  · Deutsch
Home » Blog » Article
Article

Networkdays.Intl() and Workday.Intl() in Excel 2010

12.12.2009
This article is mainly a translation of my correspondung German article. However, as I used the German version of Office 2010 for taking the screenshots and this will not be really suitable for my english speaking audience, I will first change the display and help files language in Office 2010 from German to English. This can easily be done, as the language packs are now available. Thanks to my friends on Twitter for this info.
Then I will check out the new Excel 2010 functions NETWORKDAYS.INTL() and WORKDAY.INTL().

New functions in Excel 2010

So let's first download the English language pack Microsoft Download Center. After this, we can start the setup application, which presents itself like shown in the picture below.

Office 2010 Language Pack

We keep the selected option in this window and when clicking on „Continue“, we have to accept the license terms and then we reach the following page in the installer.

Office 2010 Language Pack

As I always preferring to select the install options by myself, I choose „Customize“ and then I reach the screen for selecting the options.

Among other customizations, it may also be a good idea to also install the english proofing tools (the option was disabled).

Office 2010 Language Pack

That's all, when clicking on „Install now“, the installer starts his job.

Office 2010 Language Pack

Finally the installer succeeded and we can close the window.

Now we have to switch the language in Excel 2010, as the application is still displaying in German. For doing this, we call the Excel 2010 options from the backstage and choose „Sprache“ („Language“ in English). Then we select „English“ and finally click on „Als Standard festlegen“ („Set as default“ in Endglish). We have to close Excel and restart the application. All fine, now Excel 2010 displays in English and we can use the English names of the functions.

Office 2010 Language Pack

Let's now have a closer look on the new function WORKDAY.INTL().

New functions in Excel 2010 After calling the assistent for functions in Excel 2010, it chekced out the online help file for getting more information about the function arguments. Unfortunately, the topics in the help file seems not to be complete yet. So, I asked Google and Bing and I found some interesting topics there. I'll talk about them later in this post.

As described by the hints in the assistant, the function WORKDAY.INTL() returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. The argument „Weekend“ of the function specifies which days of the week should represent the weekend. Thsi is certainly useful in some countries where the weekend days are not on Saturday or Sunday. For example in some Arabic countries the weekend days are setted to Thursday and Friday. You can enter numeric values between 1 and 7 or 11 and 17 for the „Weekend“ argument. The last argument allows to specify special days like holidays or other days to exclude from the working calendar. You can enter the days using a string or for example select a range from your sheet.

New functions in Excel 2010

As you can see in the screenshot above, the cell D2 contains a date (12/12/2009) and cell D5 a number of days. In this case, WORKDAY.INTL() returns the 12/23/2009, exactly the workday 8 days after the current date and including the weekend days. This can also be easily checked in your calendar. The argument „Weekend“ is setted to 1, corresponding to „Saturday and Sunday“ as weekend. The follwing list shows the corresponding values for weekends including two days:

1  Saturday, Sunday
2  Sunday, Monday
3  Monday, Tuesday
4  Tuesday, Wednesday
5  Wednesday, Thursday
6  Thursday, Friday
7  Friday, Saturday

In case that the weekend will only include one day, you can use following values for the argument:

11  Sunday
12  Monday
13  Tuesday
14  Wednesday
15  Thursday
16  Friday
17  Saturday

In the second sample in the picture above I added a day to the argument „Holidays“, using a string. This increases the result by one days. If you use strings as arguments, you should use the following syntax: {"Date_1";"Date_2";…}, for example {"12/15/2009";"12/16/2009"}. However it makes more sense to select a range on your sheet.

Let's now have a look on the function NETWORKDAYS.INTL(). This function returns the number of whole workdays between two dates with custom weekend parameters. And like the function WORKDAY.INTL(), you can specify the weekend days using the „Weekend“ argument.

New functions in Excel 2010

The two samples are calculating the number of days between two dates, the last sample specifies a special day in its last argument.

At this point, I could now conclude this article if I would not have noticed a little hint in the assistant and searched for this on the net. In fact, if we read the description of the argument „Weekend“, we can notice that the hint proposes two argument types: „is a number or string specifiying when weekend occur“.

New functions in Excel 2010

When searching in the internet, I found the ECMA-376 Standard, which defines the Office Open XML Format. Microsoft submitted the new file format to ECMA International, and in December 2006, that file format was approved as ECMA-376. You can find more information about this in the post „Office Open XML, a.k.a. IS 29500“ on the Excel Team Blog. On the ECMA Website, you can find some downloadable documents describing and explaining the standard. In one of this documents, the WORKDAYS.INTL() and NETWORKDAYS.INTL() functions are mentionned in two versions. The first version accepts a numerical value for the „Weekend“ argument, the second version accepts a string as passed value. The string can only contains 0 (unsetted) and 1 (setted) as characters and must have a length of 7 characters. The first character in the string represents „Monday“ and the last „Sunday“. This means, for example, you may use „0000011“ if you like to specify Saturday and Sunday as weekend days.

New functions in Excel 2010

I added a sample to my examples in the screenshot above. The function is shown in red. Entering a string to the „Weekend“ argument also applies to the WORKDAY.INTL() function.