Crystal Reports 2011 -> Calculate number of Wednesdays (or any other day) in a date range

[Former Member]

    In some scenario we might need to calculate number of Mondays/Tuesdays etc. in a given date range.

   Let us take an example where we calculate number of Wednesdays. The below example demonstrates how to calculate number of Wednesdays in a given date range. The input dates can be database fields, formula fields or parameter fields.

The DateDiff built-in function can be used to achieve the above mentioned calculation.

The formula can be changed to count another day of the week by changing the number 4 to any number from 1 to 7, where:

Sunday ->1

Monday->2

Tuesday ->3

Wednesday->4

Thursday ->5

Friday->6

Saturday->7

The standard function is in following format:

DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)

Formula to be used:

DateDiff ('ww', currentdate, (currentdate+12), 4)

Output: 2.00

Where currentdate is today’s date i.e. 26-Dec-2011, and in the date range between today’s date and today’s date plus 12 days, there are total 2 Wednesdays.

The parameter 'ww' is used to calculate the number of firstDayOfWeek's occurring between two dates. In above example firstDayOfWeek=4 (Wednesday), hence 'ww' will count total number of Wednesday's between 2 dates.

As the output is with two decimal places, below mentioned formula can be used to replace “.00” with blank.

Replace (totext (DateDiff (‘ww’, currentdate - 1, (currentdate+22), 4)),'.00','')

Output: 2

Note - You should subtract one day from the start date if you want date ranges that start on Friday to include that first Friday in your count.  In other words if an 8 day range from Friday to Friday should count 2 Fridays.