Number of weekdays in each month 2

[Debug44]

Using Crystal 9.0 on a single data table...Connecting to UNIX

shipdate.orders
qtyshipped.orders

Grouped by date shipped (by month)

The report is summed by month (qtyshipped and count of orders)

I need a formula that calculates the # of weekdays in the group summary. (By month)

I have done this before and I do not have access to the old reports and I have forgotten how to structure the formula...
All attempts have been futile.
Any help would be greatly appreciated.
Thanks in advance

Bart

 

[rosemaryl]

Dear Bart,

This will get you the number of business days excluding weekends in a given month. I am using currentdate, all you need to do is replace the currentdates with the month start and end for your Order month and year

//begin formula
datevar fd;
datevar ld;

fd:=date(year(currentdate),month(currentdate),1);
if month(fd)=12 then ld:=date(year(fd)+1,1,1)-1
else ld:=date(year(fd),month(fd)+1,1)-1;

numbervar week ;

week:=DateDiff ("d", fd, ld) -
DateDiff ("ww", fd, ld, crSaturday) -
DateDiff ("ww", fd, ld, crSunday);

if dayofweek(fd) in [2,3,4,5,6] then week:=week+1;
week

//end formula

If you need more help, let me know.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Debug44]

Thanks for the help...Got it in 1...

Regards
Bart

 

[synapsevampire]

Another approach is to resolve this long term and keep the business rules on the database by creating a Periods table. This table would include all dates and with attributes for that date, such as Holiday, weekend, weekday, month, fiscal period, etc.

I have a FAQ here which demonstrates it and has code for generating one for a SQL Server database:

faq767-4532

It has many other uses, and is standard fare in a Data Warehouse.

-k