SSRS Report Export to excel issue for HH:mm:ss format

Question

I have to export SSRS report to excel. In my SSRS report, column has real values which I am converting to Decimal, Percentage and HH:mm:ss format for different rows as per my requirement.

I have used following format for mm:ss conversion.

Format(DateAdd("s", Fields!count.Value, "00:00"), "mm:ss")

This format is working fine in PREVIEW MODE of report. But When I am exporting to excel, the values are coming as negative.

For example, Value in preview mode is 02:38 , after exporting to Excel, it is coming as -02:38(but actual value is showing as some negative decimal value).

To fix this negative value issue, I have used following format.

=DateAdd(DateInterval.Day, -1, CDATE(DateTime.ParseExact("1/1/1900 " & (TimeSpan.FromSeconds(Fields!count.Value).ToString()),"d/M/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)))

this format is giving correct values after exporting excel but the problem is wherever DECIMAL values are there on my excel report that values are showing as #Error.

So, Is there any solution to keep format for mm:ss when export to Excel.

Answer 1

Excel has limitations on the formatting it will accept from an SSRS export. For example, you can't have leading 0's on a number. The workaround I've used in the past for this is to concatenate an apostrophe in front of the value. For example, in SSRS it would show:

'02:38

This forces Excel to display the value exactly as you formatted it in the cell. If you look in the formula box, it does show the leading apostrophe, but you can still use it in formulas as if it were a regular value.

Answer 2

I would use the FORMAT PROPERTY to format the time rather than the FORMAT FUNCTION.

I would also use 12/31/1899 as your starting date. This will allow you to add times together in excel. Using 00:00 as the starting date seems like it should work but when testing it, doesn't.

=DATEADD("s", Fields!count.Value, CDATE("1899-12-31"))