SSRS Export to Excel for Time duration HH:mm:ss over 24 hours

asked 2021-05-09T12:10:16.913+00:00
RobTyketto 1 Reputation point

Hi,

I currenty export a report to Excel which contain colums of time durations in the format of HH:mm:ss, via integer value in seconds from a SQL store proc, then an expression and format of the columns in the rdl file.

The new request is for the Excel cells containing durations values to be in the custom HH:mm:ss format rather than General or Text.
I know this is possible if the time duration is under 24hrs as the text box can be converted to a date and the format HH:mm:ss will then be applied. If it's over 24 hours I have not found it possible.

Has anyone ever achieved exporting a value over 24 hours e.g. 35:14:00 into Excel with a custom format of HH:mm:ss. thanks?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,095 questions
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2021-05-10T08:36:50.26+00:00
    Joyzhao-MSFT 15,356 Reputation points Microsoft Employee

    Hi @RobTyketto ,
    I don't think it can be achieved. When I created a value with the data type "Time" in SSMS, the value exceeded 24 hours, and the following error occurred.
    95222-01.jpg
    So I think "35:14:00" can only be exported in the form of text.
    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments