Holidays

[CPK6104]

I'm trying to exclude weekends and holidays in a date calculation. I'm using formula langage posted in tek-tips but don't think I'm using it correctly.

In the header, I have the field @Holidays

//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2010,01,01),
Date (2010,01,18),
Date (2010,06,30),
Date (2010,07,05),
Date (2010,09,06),
Date (2010,11,25),
Date (2010,11,26),
Date (2010,12,23),
Date (2010,12,24),
Date (2010,12,31),
Date (2011,01,17),
Date (2011,07,04),
Date (2011,09,05),
Date (2011,11,24),
Date (2011,11,25),
Date (2011,12,23),
Date (2011,12,26),
Date (2012,01,02),
Date (2012,01,16)
];
0

Then, I have a field called @Business Days

WhileReadingRecords;
Local DateVar Start := date({@Create DateTime});
Local DateVar End := date({@Complete DateTime});
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


I don't believe @Business Days is excluding holidays in it's final calculation. I don't think it's considering @Holidays but I don't see why. Thanks for your help.

 

[CPK6104]

Correction, here is @Business Days:

WhileReadingRecords;
Local DateVar Start := date({@Create DateTime});
Local DateVar End := date({@Complete DateTime});
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate ({@Complete DateTime} - dayofWeek({@Complete DateTime}) + 1
- ({@Create DateTime} - dayofWeek({@Create DateTime}) + 1)) /7 ) * 5;

Days := DayOfWeek({@Complete DateTime}) - DayOfWeek({@Create DateTime}) + 1 +
(if DayOfWeek({@Create DateTime}) = 1 then -1 else 0) +
(if DayOfWeek({@Complete DateTime}) = 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 {@Create DateTime} to {@Complete DateTime} then Hol:=Hol+1 );

Weeks + Days - Hol


I don't see how @Business Days references @Holidays.

 

[Madawc]

Try displaying the output of your calculation next to unselected data. That should do it.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

I tested the formula and I believe it is working. Your original formula was the one to use. After setting the variables to your actual dates, you then can just reference the variable names.

This part of the formula:

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 );


...checks to see if any of your holidays fall in a work week (mon to fri), and then uses the "Hol" variable to count when this occurs. Then Hol is subtracted from the day count in the last line:

Weeks+Days-Hol

-LB

 

[CPK6104]

Thanks for checking this. I really appreciate. And, I'll go back to the original version.