Date locales in the regional settings of the operating system
Date locales in the Excel cell format menu
The presentation of a date and its actual value are two different things. A date is a measure, it can be compared, sorted, ranked and so on. Each date value is defined uniquely and without ambiguity. The presentation of a date is only a way to visualize dates on a screen or device, and it varies depending on the device. The same date value may be presented as 01/02/2005 or 02/01/2005 and while everyone would agree the former are different, they are only showing different views of the same value. If the device is a watch, then dates are shown the way the manufacturer decided to show them. If the device is a computer, then the date will be shown by taking advantage of your operating system language, and personal locale settings. Since this article is about Excel, the remainder of this article will focus on the latter.
Excel is an application running on top of an operating system. Because of that, the way dates are presented on screen are depending on a number of layers of code, including :
This sets the terms of the game.
Date values can be any numerical measure that is comparable. You could never ever guess that 36526 is actually a value whose representation can be "1 January 2000". There is no cross-platform standard about date values, pretty much all operating systems set their own. Sometimes even applications set their own, which is luckily enough is not the case of Excel. We'll remain on Windows date values for now. The reference MSDN documentation explains the underlying definition of type DATE, reproduced here :
The DATE type is implemented as a floating-point value, measuring days from midnight, 30 December 1899. So, midnight, 31 December 1899 is represented by 1.0. Similarly, 6 AM, 1 January 1900 is represented by 2.25, and midnight, 29 December 1899 is – 1.0. However, 6 AM, 29 December 1899 is – 1.25.In other words, the integer part of the float is the amount of days that have passed since midnight, 30 December 1899. And the fractional part is the time in the day.
Using a special crafter algorithm you can go back and forth from a floating-point value to its pieces in day, month, year, hour, minutes and seconds. The ATL class COleDateTime can do that work for you. And if you ever want to use the implementation of COleDateTime without ATL, you may simply use the ::VariantTimeToSystemTime() function call, which is what COleDateTime uses.
Needless to say, all of this is not portable to non-Windows platforms, so bear with this in mind. Other platforms, and even some applications on Windows, use a different reference time than midnight, 30 December 1899. So you'll probably need to offset the value.
To tell the whole truth, Excel has two reference values for dates. The other is known as 1904/MAC and any user can change the settings of Excel so that dates use that reference point instead. Of course, the good side of this is that it's an option that works on a per workbook basis and thanks to this, developers can safely ignore it if they are controlling what gets generated (as in VBA-driven Excel code, or xlsgen code). Changing the reference point is easy : open a workbook, go in the Tools / Options / General, then check "1904 date system".
Although the (day,month,year,hour,minute,second) 6-uple is universal once you have it, the visual representation then depends on which locale you want to use to display the date.
At this point, you may decide that your code should be independent of the locale and be presented on end-user machines just like it is presented on your machine (provided what is displayed on your machine locale neutral too). There are two ways to achieve this :
The format mentioned above is what described the pattern to use to represent the date. It's locale specific. For instance, with a French locale days are represented by letter j, whereas with an English locale days are represented by letter d. Again, Excel is doing the heavy lifting, and there is a difference between how the format is internally stored (it's a locale-neutral syntax), and the way it is presented.
Neutrality is thus achieved by passing your custom date format. The format syntax is known in Excel such that letter d always represents a day. Once recognized, Excel can translate the format itself in the user interface and show the format with letter j if you have a French locale.
Alternatively, you may want to choose one of built-in date formats which are by design locale aware, ie whose representation will automatically change when an user opens the Excel with a different locale, see the asterisk (*) in the user interface of Excel. Namely, if you pass "dd/mm/yyyy" then this will be automatically recognized by Excel and will be used as a local-aware date whose representation will change with the user locale and so on. Passing a format like "dd \; mm \; yyyy" will not be recognized as a normal date flavor, and will be identified as a custom format. No locale translation or re-ordering will be performed on it. The main changes with "dd/mm/yyyy" is that when you right-click the cell and show the Format / Number menu, you'll see the custom format selected, instead of a more "regular" date format.
That being said, here comes the time to pass dates in xlsgen.
Anytime you want to pass a date, you may pass a regular label,
' VisualBasic code follows ' passing a date as a regular label worksheet.Label[row][col] = "01 March 2005".
Anytime you want to pass a floating-point value, alternatively just use a statement like worksheet.Float[row][col] = 36526.0, and pass a format of your choice. Here is an example :
' VisualBasic code follows ' passing a date as a value Dim style As IXlsStyle Set style = wksht.NewStyle ' custom format style.Format = "dd \; mm \; yyyy" style.Apply ' 1st january 2000 wksht.Float(4, 1) = 36526.0
In case you want read dates back and forth, using floating point is the preferred way since it will remain consistent over time. A typical scenario would be to create an Excel file with some floating-point based dates in it, then hand the file to people for approval/annotating, and then read the Excel file again using xlsgen. If you wrote dates as strings in the first place, and expect to read those back as strings you may encounter problems since between the first creation and the reading phases, Excel may have switched the string into a floating-point based date.
Last but not least, if you are using .NET classes, for instance the DateTime object, then you can pass a DateTime value thanks to the ToOADate() helper method. (OA is short for OLE Automation which is the date value encoding we refer to in the beginning of the article).
webpage : Excel and date and time calculations
MS knowledge base : How to use dates and times in Excel