reporting-servicesrdlcrdl

SSRS format minutes as Days:Hours:Minutes


I have Arrival Date and Departure Dates and the difference between them in minutes. I would like to format the difference between the dates as DD:HH:mm (Days:24-hours format:minutes). For instance, the minutes 3245 should be shown as 02:02:45(2 days, 2 hours and 45 minutes => 21440 + 260 + 45). I've tried DateDiff function, but could not get the exact formatting. Also I need to Sum up these at the end. Please help me with the Report Expression.


Solution

  • You need to add two columns. One will display the formatted expession and one hidden will contain the datediff calculations.

    Your calculation column will contain the datediff expression in minutes

    = DATEDIFF("n",Fields!date1.Value,Fields!date2.Value)
    

    For the total you can normally use SUM

    = SUM(DATEDIFF("n",Fields!date1.Value,Fields!date2.Value))
    

    Your expression display textboxes should refer to the relevant report items (in my example Textbox6 is for detail and Textbox9 is for total)

    = Cstr(ReportItems!Textbox6.value \ (24*60)) & ":" &
    Format( (ReportItems!Textbox6.value Mod (24*60)) \ 60, "00" ) & ":" &
    Format( (ReportItems!Textbox6.value Mod (24*60))  Mod 60,  "00" )
    
    = Cstr(ReportItems!Textbox9.value \ (24*60)) & ":" &
    Format( (ReportItems!Textbox9.value Mod (24*60)) \ 60, "00" ) & ":" &
    Format( (ReportItems!Textbox9.value Mod (24*60))  Mod 60,  "00" )
    

    enter image description here

    enter image description here

    enter image description here