The toughest things about doing custom development in Office is the fact that the more distributed the application gets the more likely it is that some obscure localization or setting will bite you in the backside.
I live and do most of my business in English Canada, within Fortress North America. Thanks to living next door to the Big Guy, and being surrounded by nothing else but big oceans, we Canadians have gradually “Americanized” our business processes in order to synchronize with them (and in order to do business with them of course). We have been able to make a lot of assumptions about what we will receive from clients in the way of data, and how they will want it to look. Therefore we have Americanized our currency formats. We do however try to hang onto “colour” and a check is a “cheque” even sometimes when we send stuff Stateside (a small statement of independence).
As a spreadsheet user and developer one of the most powerful capabilities of Microsoft Excel (and often the most painful) is date arithmetic. The capabilities of date manipulation from text to serial, changing formatting, using formatting, formulas (formulae/) and code are way too powerful….. Doing all kinds of magic with Dates and Times is among the most “fun” you can have with a spreadsheet (spoken like a true spreadsheet junkie).
But here in Canada we live in a bizarre date format regime.
The official Canadian “Short Date Standard” according to Windows is officially “DD/MM/YY” or “02/10/2007” but most Canadians use “M/D/YY” format or “MM/DD/YY” or MM.DD/YYYY” format. I have found that most people have their dates set to the U.S. format but right out of the box some people have the Canadian standard as the default.
I admit here and now that NOT building an accommodation for all potential data formats is bad form – but I live in a world where costs are always an issue and also there is the all too human tendency to cut corners to get the work done. But that’s no excuse really.
I tend to use Datevalue() function a lot. No particular reason, but that I’m used to it. Now I have had an app blow up because of using Datevalue() and assuming that the user would have their Global dates set to MM/DD/YY. Oops. If you rely on Datevalue() without caution you will get burned.
So in order to work around this problem I have used the Date() Function wherever possible in the app as it uses Date(YY,MM,DD) format or dateSerial() in code.
If there is however a need to use Datevalue() you can add this sub-routine into your startup routine the following procedure will be useful:
Sub DateFormat()
Range("DateFormat").Value = Application.International(xlDateOrder)
End Sub
where Datefornat is a named cell. This will put a 0,1 or 2 in the cell to indicate M/D/Y, D/M/Y or Y/M/D respectively. You can then use the value of “DateFormat” to do conditional formulas or code as necessary.
There are many permutations and combinations of dates and date formats you may want to use but knowing the Global Date Setting will give you the ability to accommodate nearly all variants.