Weekdays per month (1 Viewer)

[Gorio]

To calculate an average in my report, I need to determine the number of weekdays, Sundays, etc, in a month of any given year. This will allow me to divide the sum of incidents occuring on Sundays by this count to generate the average.

Any ideas on how to generate this number? Using the average function is not working for some reason. Perhaps it is since an incident does not necessarily occur on each Sunday of the month.

 

[llkhoutx]

Build you a function to calculate the numbers you want, i.e. loop through each date in a month determining the day type and counting the ones that you want.

 

[Gorio]

Would this be a function in VBA? Where does it get the information on the days in a month? I would use the table that I built, however there is no assurance that all days of the year will be found in it.

The problem with that would be that if it found 4 occurences in the table, but they only occurred on 3 out of 4 Sundays in the month, the average would be skewed too large.

 

[Pat Hartman]

Look up this article in the Microsoft knowledge base. It will get you started.

Q97757 - ACC How to Determine Number of Working Days Between Two Dates

 

[Gorio]

Thanks Pat. Though that was not the method that I've ended up using, it did give me some ideas. Unfortunately the method that I ended up using is somewhat akin to reaching around your back to scratch your elbow. It works, but there must be an easier way.