Category Archives: 13896

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.

Thicker Than A Length of 4be2

Being a
developer can be addictive fun. Sometimes you just get into the swing and
before you know it is midnight and the house is eerily quiet. But it can also
be hugely frustrating. Yesterday I had just such one of those days. I spent a
whole day on a task that should have taken an half an hour maximum.


I have
inherited the maintenance of a small application that that creates Word
documents from a set of inputs in an Excel form and spreadsheets. Now being a
good coder, the original developer created Word templates with a series of
bookmarked fields that he updated with the information collected on the Excel
sheets. I was recently asked to make a small amendment. This amendment affected
a table of prices on one of the Word outputs which shows a product number,
description and price. The client wanted the price to be appended with the text


Originally I
thought I would just update the template and add USD after the price bookmark
fields. This I did, just needed to test it then
Excel Do, Dynamic Does


getting the code to run was gave me great problems, the addin would just start
and then immediately exit. To cut a long story short, after much rooting around
(imagine trying to debug an app that immediately shuts down), I tracked the
cause of this oddity. It was all down to the fact that the code stores 3 dates
in the registry, and compares these dates at startup, shutting down if there is
some issue. Unfortunately, the dates were being stored in the registry in US
date style (mm/dd/yy), and so today’s date of 8th June (as I said,
this was yesterday) was being stored as 06/08/10 because of VBA’s idiosyncratic
handling of dates in a non-US date format, and when this was read back in the
code saw it as 6th Aug, and immediately exited.


I finally
got around this by changing the date in the registry, but will have to change
the code to save the date in a format that is not US-centric for future
changes. I can see the original developer’s logic, he is US based, the client
is US based and there would be no possibility of anyone else using the app, so
US style dates were not a problem. Not until I came along anyway.


On testing
my simple solution, I hit a  problem because
 the list of prices catered for 10 different
items. An offer may be made with less than 10, so that when the app was run,
and I only entered 3 items, the document had 7 lines with just USD on them. I
couldn’t add the text to the template Word template.


thought was to dive into the code and when the price is taken from the Excel
sheet and copied into the bookmarked fields, I would append USD at this point
(there is no concern with other currencies, this is a custom app for a solely
US-centric company).


Now, I had
never dived into the code of this app before, all previous changes that I did had
just been effected by changing the Word templates or the Excel sheets. Stepping
through the code I found that it uses named ranges on the Excel sheets that
correspond to the bookmark names in the Word template, and so just goes round
in a loop and transfers the values from the named range to the equivalent Word
bookmark. This was not good news, for me, because it meant that I would have to
test these names and look for specific names, and add USD to those values. Not
a great coding challenge, but it seemed a very poor solution to me, breaking
what was a very clean and tidy original code model. Worse, I had some similar
changes in other docs to do, and I could see it getting very messy.


Then after
dinner, my brain finally engaged. The prices in the Excel spreadsheet were
being displayed as dollar values, for instance as $1,234, and this was what was
transferred to the Word bookmark field. It finally dawned on me that all I had
to do was to change the format of the Excel field from “$”#,##0;;; to
“$”#,##0 “USD”;;; and the existing code would take care of it all for
me. A quick change of formats, a simple test run, and all was shown to be


So a 5
minute problem took me a couple of hours to resolve. At times, my denseness amazes
even me. My only thoughts were that getting over the app dates problem has slowed
my brain down, at least that is the excuse I am using.