SSRS 2008 Datediff for Working Days

ssrs-2008 datediff
Lance picture Lance · Feb 11, 2014 · Viewed 23.5k times · Source

I have seen this question asked a lot and I cannot seem to find one clear answer about

"how to calculate business days only between two dates?"

The expression below will give me the total days but I am looking to exclude Saturday and Sunday.

=DateDiff("d",Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)

I would appreciate specific help about how to accomplish this.

Thank you in advance.

Answer

Charles M. picture Charles M. · Jul 2, 2014

This code is not exactly correct. A year can start or end with either a Saturday or a Sunday. For example, 2011 starts on a Saturday and ends on a Saturday. January 1st & 2nd, 2011 are Saturday and Sunday respectively and Dec 31st, 2011 is also a Saturday. The above code does not account for this scenario. The code below is correct:

= (DateDiff(DateInterval.day,Parameters!BeginDate.Value,Parameters!EndDate.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Parameters!BeginDate.Value,Parameters!EndDate.Value)*2) 
- IIF(Weekday(Parameters!BeginDate.Value,1) = 1,1,0)
- IIF(Weekday(Parameters!BeginDate.Value,1) = 7,1,0)
- IIF(Weekday(Parameters!EndDate.Value,1) = 1,1,0)
- IIF(Weekday(Parameters!EndDate.Value,1) = 7,1,0)

Related questions

How do I format date and time on ssrs report?

on SSRS report I need to show todays date and current time i tried this =FormatDateTime(Now,"MM/dd/yyyy hh:mm tt") but this is not working for me giving an error. Anyone please help me for expression ? I …

Read More
How to get rid of blank pages in PDF exported from SSRS

I have a two-page SSRS report. When I exported it to PDF it was taking 4 pages due to its width, where the 2nd and 4th pages were displaying one of my fields from the table. I tried to set the …

Read More
SSRS Query execution failed for dataset

Have just deployed my Project on to my reporting Server. I have multiple datasets which are referencing views which exist on the db on that server. When I try to go into any report part I am getting this message: …

Read More