Count Occurence of a Weekday Between 2 Dates

[jodan]

I have 3 parameters in a report:
1. {?FromDate}
2. {?ToDate}
3. {?DayWeek} (a 3 letter representation of a day i.e. "Sun")

I need to calculate the number of times the specific {?DayWeek} day occurs between {?FromDate} and {?ToDate}. If the {?DayWeek} occurs on the {?FromDate} or {?ToDate}, it should be counted.

So between 5/2/2010 and 5/11/2010 if the {?DayWeek} param was "Tue", I need a return of 2 but if it was "Wed", I would expecct a return of 1.

I looked at other posts regarding counting business days between two dates but I can't figure a good way to modify that logic to fit this scenario. This formula will be at the beginning of the report so it can be referenced many other places.

Any help is appreciated!

 

[IanWaterman]

Try formula with

@Day
If weekdayname(dayofweek(datefield)) like {?yourparam}&'*' then 1 else 0

Then summarise this formula for your count

Ian

 

[lbass]

You could use a formula like this (changing the dayofweek parameter to a number, using the number for the day of week value and then adding the three-letter designation as a description field):

datediff("ww",{?From}-1,{?To},{?DOW})

This is one of Ken Hamady's formulas.

-LB

 

[jodan]

Thanks lbass! I knew there was a simple solution. Here is my formula that works, I placed it in the report header so the value is now available for comparison on the first record:

local numbervar ParamWeekday;
local numbervar DayCount;
If {?DaysOfWeek} = "Sun" then ParamWeekday:= 1
else if {?DaysOfWeek} = "Mon" then ParamWeekday:= 2
else if {?DaysOfWeek} = "Tue" then ParamWeekday:= 3
else if {?DaysOfWeek} = "Wed" then ParamWeekday:= 4
else if {?DaysOfWeek} = "Thu" then ParamWeekday:= 5
else if {?DaysOfWeek} = "Fri" then ParamWeekday:= 6
else if {?DaysOfWeek} = "Sat" then ParamWeekday:= 7
else ParamWeekday:= 0;
if {?DaysOfWeek} = "All" then DayCount:= {?ToDate}-{?FromDate}+1
else DayCount:= DateDiff("WW",{?FromDate},{?ToDate},ParamWeekday);
DayCount;