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