How to count Wednesdays between two dates

[home70]

Is there a way to find out the number of Wednesdays between two given dates? Thank you.

 

[Dudley]

from VB Help for datediff:

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

 

[home70]

Thanks for your reply. I already use DateDiff to get the number of weeks between dates. I now need a more exact calculation.

 

[Jon K]

Is there a way to find out the number of Wednesdays between two given dates?

DateDiff("ww", [StartDate], [EndDate], 4) -(Weekday([StartDate]) = 4)

.

 

[RuralGuy]

This link is useful as well: How many Sundays between two dates

 

[home70]

DateDiff("ww", [StartDate], [EndDate], 4) -(Weekday([StartDate]) = 4)

.

Thanks but it's not working for me. In a query it tells me that it doesn't like the last ) and as the controlsource of a text box it gives me #Name?
What do you think? Am I doing something wrong?

 

[Dudley]

Add another closing paren at the end?

 

[Dudley]

oops. scratch that.

 

[Dudley]

That string works for me. I made a form with 3 textboxes. I named one StartDate and entered 1/1/2007. I named one EndDate and entered 1/31/2007. and I entered =DateDiff("ww", [StartDate], [EndDate], 4) -(Weekday([StartDate]) = 4) in the ControlSource of the third textbox. After entering the dates in the first two textboxes, the third reads 5.

 

[Jon K]

Originally Posted by home70
Thanks but it's not working for me. In a query it tells me that it doesn't like the last ) and as the controlsource of a text box it gives me #Name?
What do you think? Am I doing something wrong?

Dudley has shown you how to do it in the control source of a text box on a form.

As for doing it in a query, please see the query in the attached database.
.

 

[home70]

Thanks to all of you for your help!
I hope you all have a good day.