Recently we observed a weird issue with SSRS export to Excel. One of the datetime column in the report was formatted using the FORMAT function as “MM-dd-yyyy HH:mm:ss” (24 hrs format). However when this report was exported to Excel, this column was converted as Text column instead of DateTime.
In the below screenshots, the first one depicts the report and the second one is the Excel output of the same.
Note the highlighted in red, the DateTime column is treated as General (Text) not as DateTime, this removes the ability to pivot or apply some formula on that column.
After couple of hours I came across an article (unfortunately forgot the URL?? ) which said that using FORMAT function removes the actual data type of the column while exporting. Instead it suggested to use FORMAT property (as in below screenshot)
That trick worked. Below screenshot is from the Excel export of the report where the formatting was done using the Property and it behaved as expected.