Modify the number of "Work Days" between any two dates

[pomster]

I have been using Ken's "Number of "Work Days" between any two dates formula for a number of years now, but a recent change of database gives me a table of "Holiday Dates" Is it possible to convert the table dates to an array to fit in with Ken's formula?

Ken's code

//Main formula
WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol


//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0


regards,


David

CRXIR2 with CRXIR2 server and SQL 2005

 

[jehanzebn]

I have had the same issue and it is still on going however I managed to skip it by using Ken's holidays array and entered date range to year 2020.

I have tried using sub report however that didn't work. Here is what I did with sub report.

I created a sub report with only one table (holidays table) then I inserted a formula into the sub report which used a shared var.

This shared datevar was then shared with the working days formula.

So it was like this

//Working days formula - You need to enter the following.
Whileprintingrecords;
.....
Shared DateVar Array Holidays;
....

//This is the formula for the subreport I used. Please note: Subreport is placed on my report header and is being suppressed. In addition to that I have tested my subreport in accordance with my database field data and they both are identical.
 
whileprintingrecords;
shared datevar array Holidays;
numbervar i := i + 1;
numbervar j := count({bank_hol.holiday_date});
 
if i <= j then (
redim preserve Holidays[j+1];
Holidays[i] := {bank_hol.holiday_date}
);

I then instered the subreport on the main report header and suppressed it.

When I ran the report it did not work.

Reason: I could not use a formula from a sub report in the record selection of the main report as the main report will process before the sub report, therefore it filters the data of the main report before the shared array is passed back to the main report.

So basically in the end I went online

URL:

http://www.bankholidaydates.co.uk/HolidayDates/year/UnitedKingdom/2006.aspx

and I copied all the holiday dates upto year 2020.

Now the report is filtering the bank holidays properly. If you want I can copy and paste all the dates and you can do the same as what I did

Regards

Jehanzeb