Sql-server – 01/01/1753 date is corrupt in Excel after export from SSRS

excelsql-server-2012ssrs-2008-r2

The date 01/01/1753 appears fine in the SSRS 208R2 report preview, but when the report is exported to Excel 2010, it appears as ########### and the value in the cell is -53689. All other dates come across just fine.

Apparently Excel accepts 01/01/1753 input and displays the dates correctly when manually entered.

Is there any method to display the SSRS reports containing the 01/01/1753 dates in Excel correctly as well?

PS: I was not able to find any relevant information on this using google. It seems to ignore the numbers and return mostly the articles on how to sort the worksheets by date.

Best Answer

Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######.

So, that is a Excel limit. Further, you can type in strings that look like dates, but Excel may not think of them as dates. For example, if I make a column of date type and enter into the columns -53689, 01/01/1753, and 01/01/1900 (using my settings) I get this:

####################
01/01/1753
            1/1/1900

So, it is easy to see that these are not all being interpreted the same by Excel.

Related Question