Count Number of weekends between 2 dates in SSRS

Problem:

There is no in-built function in SSRS where we can count the number of Saturdays and Sundays between any two dates in SSRS. This is a needed function for scenarios where we only need to get a count of working days.’

Solution:

Following is a formula that can be used for getting an accurate count of weekends.

= (((DateDiff
(DateInterval.Day,
DateAdd(DateInterval.Day,7-WeekDay(Parameters!startDate.Value),Parameters!startDate.Value),
DateAdd(DateInterval.Day,7-WeekDay(Parameters!endDate.Value),Parameters!endDate.Value).AddDays(1)) + 1)/ 7)*2)
+
iif(weekday(Parameters!endDate.Value)=7,1,0)
+
iif(weekday(Parameters!startDate.Value)=1,1,0)
-1

Here instead of Parameters!startDate.Value and Parameters!endDate.Value, you can use any other Start Date or End Date.


Share Story :