Counting no of Days formula help needed!!:)

[Pshaw]

Hi everyone,
I'm new to crystal and need some help
I have a bit of a problem getting a formula that counts the number of working days in a month. I have set up a report that has tables such as below:

Item Daily Sales Forecast Sales MTD Sales MTDFcast
1200 500 499 1200 ??

The date formulas for daily sales right up to MTD sales is easy enough as it takes the date from date of invoice.
Transaction date for forecast sales are all 1st day of the month e.g. for oct04 forecast is 01/10/2004..and foreacsted daily sales is whole month's forecast divided by numbner of working days..
i did a formula for no of working days something simple like

if ({view.TRANSDATE} = 20040701) then 22 else
if ({view.TRANSDATE} = 20040801) then 22 else
if ({view.TRANSDATE} = 20040901) then 22 else
if ({view.TRANSDATE} = 20041001) then 21 else
if ({view.TRANSDATE} = 20041101) then 22 else
if ({view.TRANSDATE} = 20041201) then 21

which is fine and all...then i got to forecast sales month todate!!
i need the daily forecast sales x no of working days rigth up to a specified date in the parameter ...e.g 13/09/04 = 9 working days from start of the month excluding the weekends..so daily forcast sales x9 days would be my mtd forecast sales....but i cant get a formula to spew out the 9...

Any help would be greatly appreciated!!

 

[Madawc]

You can identify weekends using DayOfWeek({Date}), with Saturday being 7 and Sunday 1. But this doesn't allow for bank holidays.

The standard solution is a table of non-working weekdays. You'd need to be able to add this to the database, or get someone else to add it for you.

It helps to give your Crystal version, since newer versions have extra solutions, and some extra problems. I use Crystal 8.5

Madawc Williams (East Anglia)

 

[Pshaw]

Hi Madawc,
Thanks for the post!
I'm using Crystal 8.5.
I did think about adding a table to the database...but thought it would be worth a try if i could get a formula instead of adding the table.

I'm not to concerned with public holidays actually...so i might try your suggestion of using dayofweek...see what i can come up with..

 

[Pshaw]

OOOoooo...i did it..found an excellent tip..see below for those interested...i just copied and pasted this
worked fine!

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