Count Working Weeks In Selected Month

[on-2]

I think i am missing the obvious but cannot seem to get this one....

I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).

i can calculate the number of full weeks using the control source..

=Int(DateDiff("d", Date1, Date2) / 7)

but this does not work for the 5 week months?

Any help would be greatly appreciated.

 

[TanisAgain]

This SQL will give the number of working days between 2 dates, Start and End. You have to supply the Start end End dates.

SELECT (5*(([End]-[Start])\7))+IIf(Weekday([End])=7,IIf(Weekday([Start])=1,5,7-Weekday([Start])),IIf(Weekday([Start])=1,Weekday([End])-1,IIf(Weekday([End])>=Weekday([Start]),1+Weekday([End])-Weekday([Start]),6+Weekday([End])-Weekday([Start])))) AS WorkDays

 

[on-2]

thanks for your reply. I am looking to automate an invoice generated each month which charges for the working weeks during the month (or between dates), i cant seem to apply a working days calculation to get it to work?

 

[Jon K]

I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).

=DateDiff("ww",[Date1],[Date2],2)-(Weekday([Date1])=2)

The expression returns the number of Mondays between Date1 and Date2.
.

 

[on-2]

Excellent DateDiff

Thank you Jon K that is a cracking bit of datediff.

 

[ebarrera]

John K, does the 2 represent Monday? I am trying to count weekdays for all days scheduled. For example, a class meets Mondays, Wednesdays and Sundays. Would the values in this formula be 2,4 and 0?