SSRS 2008 date and export statement to avoid confusion

Question

Suppose I have a field with a date value in a Reporting Services template, for example. =CDate("2010.12.03") , I apply the format "d" to this cell, which, according to the description, "will reflect the regional settings of the report." I am creating a report using the English language, the date is displayed as 12/03/2010 , and this is normal. Now, when I export this report to Excel, I have no idea what __ is going on.

The first scenario: the regional settings of the computer are set to English (USA). When I open an excel document, the value indicated in the cell is 12/03/2010 , as expected. When I click on it, I see that the actual value stored in the cell is 2010.12.03 , which also seems reasonable - some formatting is applied to the cell, which is not just exported as text. But when I try to figure out what type of formatting is applied by right-clicking and checking the cell format, I see that the format is "General", i.e. Not! How can it be? This, by the way, is Excel 2010, but the .xls file itself, of course.

The second scenario, where it becomes more interesting: now the computer area is set, for example, Lithuania, where the date format is 2010.12.03 . I open the same document and see 12.03.2010 . Now that just doesn't make any sense. When exporting many times, I have seen that sometimes a cell is formatted as [$-10409]mdyyyy in excel (in the "Custom" section). What does this mean, what does 10409 mean? The weirdest part of everything: if I close the document without saving, change the computer area to English (USA), open the document again, the format is now [$-10409]m/d/yyyy ! HOW is this possible! ??

Basically the same thing happens with numbers and with thousands / decimal separators - excel uses the computer area to format them, but the actual cell format may be something like [$-10409]#,##0.00;-#,##0.00 or General - again, depending on the area of ​​the computer, the direction of the wind and the temperature outside.

My question, first of all, what happens __? Secondly, what the excel document should look like according to the specifications, i.e. What does it mean to say that the format “will reflect the regional parameters of the report” in BIDS, where I chose the format “d” for the date text field, means? Does this mean that the format will be determined by the language of the report, and the result will look the same on all computers in the world (which makes sense, since other formats behave this way, i.e. if you export the date to pdf, it always remains unchanged)? If not, which apparently partially takes place in excel, why the exported date cell does not have a regional date format, i.e. the one you usually use in excel, the one that formats the date according to the area of ​​the computer

Are these some kind of excel restrictions or what? Why can't we have consistent behavior, that is, make everything sensitive to the culture of the computer while viewing a document or not, why is the actual behavior somewhere in the middle?

Answer 1

Excel uses a custom encoding for the date and uses the locale of the machine as a hint on how to format the contents. The encoding is archaic and has many specific historical errors.

This means that the errors you see often occur - you will have data that has been exported to Excel, which then has its own formatting and contents that are distorted after being opened for the first time by the actual Excel application. The problem can be anywhere along the line - perhaps the library that exports the data to Excel is not related to some of the more esoteric historical cases, or maybe Excel confuses itself along the way.

I had some success in the past with exporting dates as strings to a CSV file, devoid of formatting, and then importing them into Excel / opening them using Excel.

Answer 2

I sorted the date formatting issue:

• adding calculated fields for dates:

   =IIF(IsNothing(Fields!Date_Delivery_Confirmed.Value),nothing,DateSerial(DatePart("yyyy",Fields!Date_Delivery_Confirmed.Value), DatePart("m",Fields!Date_Delivery_Confirmed.Value),DatePart("d",Fields!Date_Delivery_Confirmed.Value))) 

• set cell Text field as dateformat

• How I use * dateformats (localized) I checked my server laptop and the report is configured in the right language

• I aligned the elements vertically in the report so that it (visual spaces) would not create extra empty columns when exporting to excel. Since 2 excel columns combined with 1 date cell will never get any format, but "General format"