The US Isn’t The Whole World?

Microsoft sneaked a couple of new functions into Excel 2010. These functions are WORKDAY.INTL and NETWORKDAYS.INTL.

They appear to be addressing the age-old question of handling different working day patterns around the globe. Whilst we have Saturday and Sunday off, some countries may have Friday and Saturday. And of course, when planning work, some poor souls only get a single day of the week off. These new functions provide a new optional argument, weekend, to specify which days are to be treated as weekend days.

This should remove those complex formula solutions to handle this particular problem.

Unfortunately, MS as ever seem to have over-engineered it.

The help describes the weekend argument thus

Weekend  Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur

which suggests that we specify what days are in our weekend. But how do we define them?

Later the help provides a table of weekend values,

weekend-number Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday 
4 Tuesday, Wednesday 
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday 
11 Sunday only
12 Monday only
13 Tuesday only
12 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

and later its says …

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string.

They present a table of decimal values then tell me that Weekend is a string of day indicators. Okay, I admit, it did say earlier that weekend is a weekend number or string that specifies when weekends occur, but why the need for two ways of telling it which days? And where is Monday, Tuesday and Wednesday as a combination in the table of values?

So I started looking at this, and my first thought was that weekend was a binary string, and the table above was the decimal numbers associated with the binary string.

First, in the string they have 1 representing a non-working day and 0 or a working day. I appreciate they call it weekends, but that does strike me as very odd.

Then, the decimal table presented doesn’t map to the binary values. Even allowing for their upside down logic, Saturday and Sunday weekends, or working days of Mon, Tue, Wed, Thu and Fri would be 0000011 which is 3, not 1.

The table of values shown doesn’t show all possibilities, such as weekend days of Saturday, Sunday and Monday, but the weekend string suggests that it will support this.

I can’t figure out that table of values, so even if it does support more than 2 weekend days, it doesn’t seem possible to use a decimal value to use in such circumstances. If I enter =WORKDAY.INTL(–“2010-09-06”,5,8), it returns #NUM.

All in all, I cannot help but conclude that having two ways of representing the weekends is pointless, the decimal values are limited, and the weekend string representation is so much simpler to envisage.

I have played with it, and the weekend string seems to work. =WORKDAY.INTL(–“2010-09-06″,5,”0010011”) gave me Tue 14-Sep, which is Tuesday, Thursday, Friday, Monday and Tuesday past the Monday of 6th Sep. It caters nicely for holidays as well.

2 thoughts on “The US Isn’t The Whole World?

  1. Thanks for that write-up, Bob!

    Seems to me MS should have either allowed us to specify the working weekdays (or the non-working weekdays) exactly. For example, if the argument specifies the working days:

    1234567 = all days are working days
    23456 = Mon-Fri are working days
    12356 = Sun, Mon, Tue, Thu, Fri are working days

    This could have been so easy to explain 🙂

  2. Hi Patrick,

    I originally thought along those lines, but concluded that to be consistent with other functions they would then need a return_type argument, defining first day of the week. My guess is that they didn’t want to do this, so they invented a new wheel, but being MS they couldn’t stop at one, they had to have two!

Leave a Reply

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