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.