8

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.

E.S
  • 536
  • 1
  • 9
  • 20
Lance
  • 125
  • 1
  • 3
  • 7
  • possible duplicate of [Business Days calculation](http://stackoverflow.com/questions/7444846/business-days-calculation) – Tony Hopkinson Feb 11 '14 at 16:45
  • If I use the query in the other post, where I do place that query in SSRS? and how do I reference it within the textbox? – Lance Feb 11 '14 at 16:56
  • Best bet would be to create a function, I should think. That's a different question to how to calculate business days though. Must admit I assumed DateDiff was the standard sql function? – Tony Hopkinson Feb 11 '14 at 17:03
  • Thanks - DateDiff is a standard SQL function. However, excluding weekends from that calculation is not standard in SQL. – Lance Feb 11 '14 at 17:06

4 Answers4

7

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)
Mahib
  • 3,977
  • 5
  • 53
  • 62
Charles M.
  • 71
  • 1
  • 2
3

The SQL in the link (Number of working days between two dates) translated for SSRS: Hopefully this will give you a good place to start. Type this into the expression for the textbox.

=(DateDiff(DateInterval.day,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)+1)
-(DateDiff(DateInterval.WeekOfYear,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)*2)
-(iif(Weekday(Parameters!STARTDATE.Value) = 7,1,0)
-(iif(Weekday(Parameters!ENDDATE.Value) = 6,1,0))-1)
Community
  • 1
  • 1
Pops
  • 468
  • 2
  • 15
1

The easy way getting workingdays between two dates is:

DateDiff(DD, BeginDate, EndDate) - DateDiff(WK, Begindate, EndDate)*2
4b0
  • 21,981
  • 30
  • 95
  • 142
Theo Buitenhuis
  • 11
  • 1
0

Below code worked for me properly in all the scenarios

= (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!EndDate.Value,1) = 7,1,0)
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Mohith
  • 1