Calculating the number of Business Days between two dates

[peggysuebaker]

I have my formula below. I copied this from faq767-4465 posted 21 Nov 03. I get an error saying "date-time is required" here for the StartDate field I am using. It shows as a date field in Crystal so I'm not sure what I am suppose to do now. I usually make very basic reports so please try to explain details for me if you are able to help. Thanks so much.



WhilePrintingRecords;
//Set the values of Start Date and End Date
DateVar StartDate:= Date({InitialContact.DateReceivedCC});
DateVar EndDate:= Date({InitialContact.AttemptDate1});

//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(2011,01,01),
Date(2011,01,18),
Date(2011,05,31),
Date(2011,07,05),
Date(2011,09,06),
Date(2011,11,11),
Date(2011,11,25),
Date(2011,11,26),
Date(2011,12,24),
Date(2011,12,31));

//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 (DaysDiff,0,"");

 

[lbass]

It probably is already a date field, so just remove the date().

-LB

 

[peggysuebaker]

That worked, thanks so much. But, now it gives me "true" instead of the number of days. How do I get the actual number?

 

[lbass]

You commented out the final line. It should be:

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

But why not just leave it as a number:

DaysDiff

...which you can format anyway you like.

-LB

 

[peggysuebaker]

I am placing my code again so you can see changes made. I have then placed a sampling of the data I receive back. As you will see it is not correct. Subtracting Attempt Date from Date Received 7/13/2011 - 7/12/0211 is giving result of -5. I am getting different value when it is only one day difference as you can see from the sample below. I wish I know more about this but am trying to learn. I really do appreciate the time you are giving me and your knowledge. Thanks again, Peggysue


WhilePrintingRecords;
//Set the values of Start Date and End Date

WhilePrintingRecords;
//Set the values of Start Date and End Date
DateVar StartDate:= {InitialContact.AttemptDate1};
DateVar EndDate:= {InitialContact.DateRiskScreen};

//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(2011,01,01),
Date(2011,01,18),
Date(2011,05,31),
Date(2011,07,05),
Date(2011,09,06),
Date(2011,11,11),
Date(2011,11,25),
Date(2011,11,26),
Date(2011,12,24),
Date(2011,12,31));

//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
DaysDiff



SAMPLE COPY OF REPORT
DateReceivedCHD DateDifference
7/12/2011 7/13/2011 -5.00
5/9/2011 5/13/2011 -11.00
5/18/2011
6/29/2011 6/30/2011 -2.00
6/22/2011 6/23/2011 -3.00
5/25/2011 5/27/2011 -2.00
5/18/2011 5/27/2011 -8.00
5/24/2011 5/31/2011 -6.00
7/27/2011 7/28/2011 -24.00
4/25/2011 5/3/2011 -10.00
3/26/2008 6/28/2011 -853.00
6/15/2011
6/15/2011 6/16/2011 -6.00

 

[lbass]

I can't really troubleshoot your situation. Try Ken Hamady's solution instead:

http://www.kenhamady.com/form01.shtml

-LB

 

[peggysuebaker]

After much searching on the web I was able to get an answer from our IT department in Tallahassee that worked. I thought I would share so if anyone else looks for this it may help them. Thanks again for all your efforts.


Local NumberVar DayCount;
Local NumberVar WorkDays;



//List of 10 Public Holiday for 2011



Global DateVar Array Calendar := MakeArray(
Date(2011,01,01),
Date(2011,01,18),
Date(2011,05,31),
Date(2011,07,05),
Date(2011,09,06),
Date(2011,11,11),
Date(2011,11,25),
Date(2011,11,26),
Date(2011,12,24),
Date(2011,12,31));



Local Datetimevar Newdays;



Local NumberVar WorkDays;



WorkDays := DateDiff ('d', {InitialContact.DateReceivedCHD},{InitialContact.AttemptDate1});
Newdays := {InitialContact.DateReceivedCHD};



for DayCount := 1 to DateDiff ('d', {InitialContact.DateReceivedCHD},{InitialContact.AttemptDate1}) do
(
if DayOfWeek (Newdays) = 6 or DayOfWeek (Newdays) = 7 then
WorkDays := WorkDays - 1
else
if Newdays in Calendar then
WorkDays := WorkDays - 1;

Newdays := DateAdd("d", DayCount, {InitialContact.DateReceivedCHD});



);



WorkDays