calculate number of working weeks between two dates

[pb21]

I want to calculate the number of full working weeks (Monday to friday) between two dates. I want to ignore part weeks.

I had a search online and on the forum but it didnt return anything.

ex 1/7/05 to 31/7/05 would return 4



regards in advance
peter

 

[Mile-O]

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

 

[modest]

What you want to do is check what day of the week Date1 is and if it's not Monday loop through and keep adding a day to it until you get to monday.
Then if Date2 is Friday, Saturday, or Sunday keep adding a day until Date2 is on a Monday, if it is already on a Monday don't touch it, and if it's any other day (Tues,Wed,Thurs) then subtract a day from it until it is a monday.

Then call
DateDiff("w", Date1, Date2)
Just realize this will call full working weeks. So if start on Tuesday and end Friday, it doesn't count it as a week.

 

[dt01pqt]

There isplenty of refrance out there
http://www.databasejournal.com/features/msaccess/article.php/1552691