Determine what week a day is in

[Jeff Garrison]

Hello all...

A quick question...

I'm trying to create a report that shows how many items occur within a week.
See the table:
Project Contractor Date
7201 25 8/1/07
7201 25 8/3/07
7201 25 8/5/07
7201 25 8/9/07
7201 78 8/1/07
7201 78 8/15/07
7201 78 8/21/07

The end result is:

Week Week Begin Date Week End Date Project
Contractor Occurences
30 7/29/07 8/4/07
7201 25 2
30 7/29/07 8/4/07
7201 78 1
31 8/5/07 8/11/07
7201 25 2
32 8/12/07 8/18/07
7201 78 1
33 8/19/07 8/25/07 7201
78 1

etc...

What is the best way to accomplish this?

Thanks.

Jeff G

 

[Jerry Whittle]

Debug.Print DatePart("ww",#7/29/07#,,3) = 30

Debug.Print DatePart("ww",#7/29/07#) = 31

DateAdd("ww",datepart("ww",#7/29/07#),CDate("1/1/" & Year(#7/29/07#))) =
8/6/2007

DateAdd("ww",datepart("ww",#7/29/07#)+1,CDate("1/1/" & Year(#7/29/07#)))-1 =
8/12/2007