How do I determine how many weekdays in a month

[Yumster]

I am using Seagate Info 7 and trying to create a formula to determine how many weekdays are in a month. If I was using Crystal 8 I could use the Basic Syntax and do loops but I have not found anything about using loops in Seagate Info 7. I was just going to loop through the dates in a month and use the 'IsBusinessDay() function. Can any one help me with this?

Thanks in advance
Randy

 

[jleboeuf]

I use this formula, if you want more explanation mail me.

NumberVar Hol:= 0;

//Figure the number of Calendar "Rows" involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start)
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(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(2002,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2002,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
Etc...
//Assemble the adjusted work days
Tot:=Weeks + Days - Hol;
If Tot < -1000 then 0
else if Tot > 1000 then 0
else Tot

 

[jleboeuf]

Sorry second Post, complete this time

WhilePrintingRecords;
DateVar Start := {@Date_termine};
DateVar End := {@Date_Promise};
Numbervar Tot;
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;

//Figure the number of Calendar &quot;Rows&quot; involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start)
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(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(2002,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2002,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
etc...

//Assemble the adjusted work days
Tot:=Weeks + Days - Hol;
If Tot < -1000 then 0
else if Tot > 1000 then 0
else Tot


Hope this help !
Jean-Paul Leboeuf

jleboeuf@iquebec.com

 

[kenhamady]

Yes, this formula is from faq149-243 , finding business days between dates. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Yumster]

Thanks Jean-Paul,

This formula works great except it had one less business days in the month than it should have. On the Tot assignment statement I added 1 and now it is showing the correct business days in the month.

Thanks again
Randy

 

[kenhamady]

Yumster,

I wrote the formula you are using and I am surprised that you have to add one day. The formula should count both your start date, your end date, and all business dates in between.

Adding one may work in this month, but if there is an underlying problem you may have an error in another month.

As a test, if you simply subtract your start date from your end date do you get the number of calendar days in the month? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Yumster]

Ken,
When I subtract my start date from end date I get 1 less day than what is in the month. Ex. subtract 3/1/2002 from 3/31/2002 I get 30. This would be because the subtraction isn't inclusive so adding 1 more day is what is needed.

Randy

 

[kenhamady]

That is true, but the formula is designed to include both the start and end date, already. That is what makes me a bit concerned. Unless the version of the formula has been modified (you can compare it to the original in the FAQ above) it shouldn't need to have 1 added.

Are you using any holidays in your formula? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Yumster]

Ken,
I have not included the holiday piece yet but I will after I talk to the end users to find out exactly which holidays they want to exclude.

I will also check the FAQ above to for any differences. I did test this one against 3 different months and it worked just fine.

Thanks
Randy

 

[Yumster]

Ken,
I just took a look at you formula on the FAQ and it is correct. The formula supplied by Jean-Paul left out the '+1' on the 'Days:=' assignment statement.

Randy

 

[kenhamady]

That makes more sense. I recommend the original formula. While yours might do the exact same thing, I know the original has been tested in all of the possible combinations. I am surprised at how many times my impecable theory doesn't actually work in practice, and I have learned the importance of testing it in as many scenarios as possible. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Yumster]

Ken,
I had already changed it to match yours. This is a very useful formula and kudos to you for creating it.

Thanks
Randy

 

[BFAGG]

Saw this one late but some people may be interested in a very simple formula for working days, hope this helps someone.

Bill

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.



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)