Week Numbers between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find all the week number (week of the year) between two dates. Can
anyone point me in the right direction?

Thanks again in advance,
 
Hmm, not sure about "between two dates", but to get a week number of certain
date you can use DatePart() function:

?DatePart("ww", Date())
 
That's kind of where I started. In order for that to work I would need to get
every date that was between the 2 dates, then use the DatePart Function to
find out what week it was. I think we're going in the right direction but
missing some vital piece.

Thanks,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


Alex Dybenko said:
Hmm, not sure about "between two dates", but to get a week number of certain
date you can use DatePart() function:

?DatePart("ww", Date())
Click to expand...
 
I got this working. I ended up using some VBA code to loop through the dates
and write the week that way. It works pretty well.
 
That's kind of where I started. In order for
that to work I would need to get every date
that was between the 2 dates, then use the
DatePart Function to find out what week it
was.
Hunh?

I think we're going in the right direction but
missing some vital piece.
Click to expand...

Ayup! Missing some vital piece.

You get the week number of the first date, the week number of the second
date, and use them to generate a list of the week numbers between them, if
that is what you need. Of course, there would be some questions, like "Do
you want a list of the week numbers? How do you want them saved, if at all?"

For simplicity, I didn't put all the hoops you'd have to jump through to
accomodate dates in different years (but you could accommodate that) or
dates more than 100 weeks apart (but you could accommodate that, too)

As to how you generate the list, here's some code that works for a couple of
values, complete with lots of Debug.Prints to show what's going on (not
"exhaustively" tested), and a little extra code to start storing the values
in the first member of a 100 member array (since I didn't know what you
wanted to do with the numbers once you had them):

Function ListWeeks(datStart As Date, datEnd As Date) As Integer
Dim aWeeks(1 To 100) As Integer
Dim i As Integer, j As Integer, k As Integer
Debug.Print "Start Week: "; CInt(DatePart("ww", datStart))
Debug.Print "End Week: "; CInt(DatePart("ww", datEnd))
j = 1 - CInt(DatePart("ww", datStart))
Debug.Print j
For i = CInt(DatePart("ww", datStart)) To CInt(DatePart("ww", datEnd))
aWeeks(j + i) = i
Debug.Print "Aweeks("; j + i; ")= "; i
k = k + 1
Next
ListWeeks = k
End Function

Paste it into a standard module, and run it from the Immediate Window with:

? ListWeeks(#6/16/2005#,#12/16/2005#)

as your first test values.

It doesn't look quite as intimidating if you strip out all the Debug.Print
stuff:

Function SimpWeeks(datStart As Date, datEnd As Date) As Integer
Dim aWeeks(1 To 100) As Integer
Dim i As Integer, j As Integer, k As Integer
j = 1 - CInt(DatePart("ww", datStart)) 'adjustment to make array index
For i = CInt(DatePart("ww", datStart)) To CInt(DatePart("ww", datEnd))
aWeeks(j + i) = i
k = k + 1
Next
SimpWeeks = k
End Function

Larry Linson
Microsoft Access MVP
 
Thanks Larry! I'll tell you what I'm doing with this info...

I have a table that contains resource committments. The table contains
multiple committments(rows) for each resource and each row contains a start
date, an end date and the average weekly hours for the committment. When the
user, a resource manager goes to assign a resource to a particular project I
want to display the resource's existing committments in a chart so they can
determine the resources availablity. So for each row I need to determine the
weeks between the start and stop dates and place the hours into the
appropriate weeks. What I ended up doing is looping through each day between
the two dates and writing the week number to a second table with the
associated hours. Then I use that table as the data source for my chart. Your
method is more elegant (of course) since I don't have to delete the rows in
my table each time I change the resource I'm viewing. My only concern is, can
I use the array as the data source for my chart or would I have to write the
array to a table for use?

Anyway, I have said it before, and I'm sure I'll say it again... Microsoft
ought to be paying you guys!!!

Thanks,

mpj
 
Back
Top