CODEWITHSUNDEEP
reporting-servicesssrs-2016
Bucki
Bucki

Reputation: 43

Calculating working days between 2 dates in SSRS 2016

I am using the below code, found on this website. It somehow works but not properly when the FromDate falls on a Friday as it adds another day on top.

See the screenshot for the demonstration.
What is it wrong in the code itself? 

= (DateDiff(DateInterval.day,Fields!FromDate.Value,Fields!UntilDate.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Fields!FromDate.Value,Fields!UntilDate.Value)*2)
- IIF(Weekday(Fields!FromDate.Value,1) = 1,1,0)
- IIF(Weekday(Fields!FromDate.Value,1) = 7,1,0)
- IIF(Weekday(Fields!UntilDate.Value,1) = 1,1,0)
- IIF(Weekday(Fields!UntilDate.Value,1) = 7,1,0)

enter image description here

Upvotes: 0

Views: 115

Answers (2)

Bucki
Bucki

Reputation: 43

SOLUTION

In my case I had to introduce the CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")) in order for the days to be calculated correctly 

I had to use the CDATE() in the code:
=(DateDiff(DateInterval.day,CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")), 
CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")))+1)
-(DateDiff(DateInterval.WeekOfYear,CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")), 
CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")))*2) 
- IIF(Weekday( CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")),1) = 1,1,0)
- IIF(Weekday( CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")),1) = 7,1,0)
- IIF(Weekday( CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")),1) = 1,1,0)
- IIF(Weekday( CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")),1) = 7,1,0)

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10860

The code is OK. The problem is that your computer may be using a different First Day of Week in the Region and Language settings than the computer that the code was made on (or mine).

enter image description here

To test, set a text box to =WEEKDAY(TODAY). The computer this code works on has 1 as Sunday, which would make today (Wednesday) a 4. My guess is that you get 3.

If your first day of the week is Monday, you'd need to change the weekdays to 6 and 7 rather than 1 and 7 that it currently uses. 

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

If it's a Saturday, you'd need to use 1 and 2 as the weekend days.

Upvotes: 1

Related Questions