Geek Answers Handbook

SSRS 2008 Datediff for business days

I saw that this question asked a lot, and I can not find a definite answer about

"How to calculate working days between two dates only?"

The expression below will give me common days, but I want to exclude Saturday and Sunday.

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

I would appreciate concrete help on how to do this.

Thanks in advance.

+7
datediff ssrs-2008
source share
2 answers

SQL in the link ( Number of working days between two dates ) for SSRS: We hope this gives you a good place to start. Enter this in the expression for the text box.

 =(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)
+3
source share

This code is not entirely correct. A year can begin or end with either Saturday or Sunday. For example, 2011 starts on Saturday and ends on Saturday. January 1 and 2, 2011 - Saturday and Sunday, respectively, and December 31, 2011 is also Saturday. The above code does not take this scenario into account. The correct code is:

 = (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)
+5
source share

More articles:


All Articles