Total number of days between two dates.


  • Hi,

    I am using report builder 3.0

    I have two date fileds (Datein and Dateout) I require the total number of days between these two dates, including day in and day out.

    I have tried the datediff function but does not give me total number of days correctly.

    =DateDiff("d",Fields!DateIN.Value,Fields!DateOut.Value) + 1

    Example results.

    DateIn                         DateOut                          Day Total

    19/12/2012 14:39:59   31/12/2012 09:01:49          12
    19/12/2012 14:40:40   30/12/2012 21:56:43          12

    Would like the first row above, day total to be 13.

    I have tried the DatePart function but this fails if dates spans more than 1 year.

    =DatePart(DateInterval.DayOfYear,Fields!DateIn.value) - DatePart(DateInterval.DayOfYear,Fields!DateOut.Value) +1


    DateIn                          DateOut                         Day Total

    18/12/2012 15:41:14    02/01/2013 14:01:46       -350
    19/12/2012 14:39:59    31/12/2012 09:01:49         13

    Any help would be appreciated.


    Tuesday, January 08, 2013 10:45 AM


All replies

  • Hi,

    Truncate the date to ignore the time using:

    =DateDiff("d",Format(Fields!DateIN.Value,"Short Date"),Format(Fields!DateOut.Value,"Short Date")) + 1

    Hope this helps!

    k r o o t z

    Tuesday, January 08, 2013 11:30 AM
  • Thank you very much Ktootz.

    It appears to work ok.

    Once again help much appreciated.

    Thank you

    Tuesday, January 08, 2013 11:44 AM
  • Hi Steve,

    Thanks for your feedback. I am glad to hear that the issue has been resolved.

    Since Krootz's post has helped you to resolve the issue, I have marked it as the answer. If you have any questions, please feel free to ask.


    Mike Yin
    TechNet Community Support

    Wednesday, January 09, 2013 6:59 AM