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 USD.


 


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 thenExcel Do, Dynamic Does


 


Unfortunately, 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.


 


Next 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 working.


 


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.