How to count number of Wednesdays between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
Is there a way to find out the number of Wednesdays between two given dates?
Thank you.
Click to expand...

Paste the following user defined function into a new module.

Public Function CountWednesdays(dteStart As Date, dteEnd As Date) As
Integer
Dim intX As Integer

Dim dteDate As Date
dteDate = dteStart

Do While dteDate <= dteEnd
If Weekday(dteDate) = vbWednesday Then
intX = intX + 1
End If
dteDate = dteDate + 1
Loop

CountWednesdays = intX

End Function

You can call it from a query....
CountDays:CountWednesdays([DateFieldStart],[DateFieldEnd])

or directly in an unbouind control on a form or report...
=CountWednesdays([DateFieldStart],[DateFieldEnd])
 
Might be a good time to think about using a calendar tables.

My calendar table has these fields:

CREATE TABLE Calendar" & _
(calendar_date DATETIME NOT NULL PRIMARY KEY,_
weekday_nbr INTEGER NOT NULL,
weekday_name VARCHAR (10) NOT NULL,
work_day INTEGER DEFAULT 1 NOT NULL,
workday_nbr INTEGER NULL,
holiday INTEGER DEFAULT 0 NOT NULL,
"holiday_name VARCHAR (50) NULL);"

So the parameter query is:

Query: WednesdaysBetween

PARAMETERS from_date DateTime, to_date DateTime;
SELECT COUNT(* ) AS WednesdayCount
FROM Calendar AS c
HAVING c.weekday_nbr = 4
AND c.calendar_date BETWEEN [from_date]
AND [to_date];

Query: WorkdaysBetween

PARAMETERS from_date DateTime, to_date DateTime;
SELECT (c2.workday_nbr - c1.workday_nbr) AS Workdays
FROM Calendar AS C1,
Calendar AS c2
WHERE (((c1.calendar_date) = [from_date])
AND ((c2.calendar_date) = [to_date]));

Query: WorkdateOffest

PARAMETERS base_date DateTime, off_set Short;
SELECT c2.calendar_date
FROM Calendar AS c1,
Calendar AS c2
WHERE (((c1.calendar_date) = [base_date])
AND ((c2.workday_nbr - c1.workday_nbr) = [off_set]))
GROUP BY c2.calendar_date;
 
Michael Gramelspacher,
I'll need a little while to try to take this in. I don't know if I'll use
it now but it seems like a good idea.
Thanks.
 
DateDiff("w", startingDate, endingDate, vbWednesday)


Well, you may have to adjust (+ 1) if the ending date is a Wednesday and
you want to include it too.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top