Business Days 2

[sgp_imported]

How would one calculate the number of weekdays (or if possible business days) in a given month?

Thanks,

 

[TheBlondOne]

Hi there, i had this problem a while ago and this is the formula that i used.

The formula counts both the starting date and the ending date if they are both work days. So if you start Monday and end Tuesday, that is 2 days.

Whileprintingrecords;
Datevar Start:={table.StartDate};
Datevar End :={table.EndDate};
Numberva Weeks;
Numbervar Days;
Numbervar Hol:=0;

weeks:=(Truncate(End - dayofweek(End) + 1 - (start - dayofweek(Start) + 1))/7)*5;

Days:=DayOfWeek(end) - Daysofweek(Start) + 1
+ (if dayofweek(start) = 1 then -1 else 0) //adjust for starting on sunday:
+(if daypfweek(end) = 7 then -1 else 0); //adjust for ending on a saturday:

//Adjust for Holidays in the period between the start and end dates:

if Date(Insert bank holiday) in start to end then Hol:=Hol + 1 else Hol:=Hol;//repeat for the number of holidays that fall between the 2 dates:

Weeks + Days - Hol

 

[BFAGG]

SteveHill, I copied your formula for future use, good stuff!

mOrgan, Here is aanother simple variation that may be useful:

The following formula calculates the number of working days in any given period. This is based on a Monday to Friday working week and does not allow for Holidays.

The function dayofweek is a Crystal reports function that returns a whole number (1 - 7) representing the days Sunday (day 1) to Saturday (day 7).

The function truncate is a Crystal reports function that rounds down the result of a calulation, always producing a whole number. Formula follows:



if (dayofweek(Start Date) > dayofweek(End Date) then

((End Date - Start Date) -
(truncate((End Date - Start Date)/7)*2)-1) else

if (dayofweek(Start Date) <= dayofweek(End Date)) then

((End Date - Start Date) -
(truncate((End Date - Start Date)/7)*2)+1)

 

[sgp_imported]

I found a solution on the crystaldecisions website under &quot;businessdays&quot;. You must download a file &quot;businessdays.zip&quot;.

Once you have loaded the exec. file it looks like this:
(as you see below it loads a text file for holidays)
------------------------------------------------------------
WhilePrintingRecords;
wdaysclass1wdloadfile (&quot;c:\my documents\databases\holidays.txt&quot;
datevar startdate:= date(2001,01,01);
datevar enddate:= date(2001,12,31);
stringvar workingdays:= &quot;-23456-&quot;;
numbervar totaldays:=
wdaysclass1wdnumworkdays(startdate,enddate,workingdays);