SSRS format minutes as Days:Hours:Minutes

Question

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.

Answer 1

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" )