Count Business Days with a Parameter

[temple11]

Hello,

First, I'm a Crystal Novice. That said, I am looking for a way to determine the number of business days in a given date range. However, the date range is variable and set by parameter when running the report.

I am using the Business Days/Holidays code found in one of the FAQs but I don't know how to modify it to reflect the values entered in the parameter for the StartDate and EndDate.

FAQ:

http://www.tek-tips.com/faqs.cfm?fid=4465

Here is the code from the FAQ:

WhilePrintingRecords;
//Set the values of Start Date and End Date
DateVar StartDate := Date(2003,01,01);
DateVar EndDate := Date(2003,12,31);

//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",StartDate,EndDate) -
DateDiff("ww",StartDate,EndDate,crsaturday) -
DateDiff("ww",StartDate,EndDate,crsunday);

//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
Date(2003,01,01),
Date(2003,01,02),
Date(2003,12,25),
Date(2003,12,26),
Date(2003,07,07));

//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in StartDate to EndDate then DaysDiff := DaysDiff -1;

//Display result to 0 decimal places and no thousand separator
totext(DaysDiff,0,"");


Any help would be appreciated.

thanks!

 

[dgillz]

Delete the first formula, it will not be needed. Then change your second formula:

//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",{?StartDate},{?EndDate}) - 
DateDiff("ww",{?StartDate},{?EndDate},crsaturday) - 
DateDiff("ww",{?StartDate},{?EndDate},crsunday);

The ? will make the formula look at the values of the parameters the user entered rather than a hard coded date as in the example provided.

Finally, change your fourth formula to also look at the parameter values:

//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in {?StartDate} to {?EndDate} then DaysDiff := DaysDiff -1;);

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[temple11]

I replaced the code as suggested (I think) which resulted in the following formula:

//Find out the difference in days and subtract the weekends
NumberVar DaysDiff := DateDiff("d",{?StartDate},{?EndDate}) - 
DateDiff("ww",{?StartDate},{?EndDate},crsaturday) - 
DateDiff("ww",{?StartDate},{?EndDate},crsunday);

//Create an array of Holiday dates
Local DateVar Array Holidays := MakeArray(
Date(2006,01,01),
Date(2006,01,02),
Date(2006,12,25),
Date(2006,12,26),
Date(2006,07,04));

//Loop through the array checking if each holiday is within the dates
Numbervar Counter := 0;
While UBound(Holidays) <> Counter do
(Counter := Counter + 1;
if Not(dayofweek(Holidays[Counter]) in [1,7]) and
Holidays[Counter] in {?StartDate} to {?EndDate} then DaysDiff := DaysDiff -1;);

//Display result to 0 decimal places and no thousand separator
totext(DaysDiff,0,"");

Unfortunately, when i checked the formula it kicked back an error on {?StartDate} saying "This field name is not known".

any ideas?

This is on Crystal 10

thanks!

 

[dgillz]

You must create 2 parameter fields called StartDate and EndDate first. Make sure the datatype is date.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[temple11]

Brilliant! That worked perfectly. Thanks so much!

 

[temple11]

One last question. I'm trying to determine the total number of normal work hours in a given period (BusinessDays * 8) so that i can determine the percent utilization of that time based on hours reported in a given period.

Example:

?StartDate = 10/8/06
?EndDate = 10/14/06

Business days result = 5
5*8 = 40 <- Standard Hours available
Reported Hours = 50 hours

(Reported Hours)%(Standard Hours)= 125%

hopefully this makes sense and doesn't negate the help you have already provided.

Thanks!

 

[dgillz]

So multiply your result times 40 for for Std hours available. I assume you can handle that without help. Then the reporter hours is either a grant total or a summary total I assume, correct?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[temple11]

I thought I would be able to as well. when i try to multiply the result of the Business Days formula it errors and says "A number, currency or amount is required here" and points to the {@BusinessDays} portion.

I believe this is because of the toText conversion at the end of the BusinessDays formula. I could be way off there.

the syntax i am using to multiply the available hours is:

{@BusinessDays}*8

 

[lbass]

Use DaysDiff in your calculation, before converting it to to text (if you need to convert it at all).

-LB