Total number of days between two dates.

Question

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

Example

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.

Steve







Answer 1

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

Answer 2

Thank you very much Ktootz.

It appears to work ok.

Once again help much appreciated.

Thank you

Answer 3

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.

Regards,

---

Mike Yin
TechNet Community Support