Week of Month - Datepart

[AKMonkeyboy]

I know I've done this before but can't remember how. I need to pull the week number of a month i.e. 2/6 is the first week of Feb, while 2/8 is the second week. I know DatePart("ww",[Date]) gives me the week number, but that's week of year.

What's the function for week of month?

 

[scriverb]

I don't think that there is a formal function for what you want but here is one that might work:

Switch(Day([Date])<8,1,Day([Date]}<15,2,Day([Date]<22,3,Day([date])<29,4,True,5)

This will give you a value 1 thru 5 depending upon the day of the month.

Post back if this isn't what you were looking for.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[AKMonkeyboy]

I'm sure what I did wasn't so "involved", but I'm willing to try it.

I pasted your code into my query (I had to fix a typo - you have a french bracket } where there should be a paranthesis after the second occourance of "Date") I get a "wrong number of arguments" error.

I'm not familiar with the "Switch" command, so I'm not sure what it's looking for.

Perhaps it's something I did?

Thanks for your help.

A

 

[scriverb]

Sorry about the typo but I also left out a right Paren. Copy and paste this code into a new column in your query:

WeekOfMonth: Switch(Day([Date])<8,1,Day([Date])<15,2,Day([Date])<22,3,Day([Date])<29,4,True,5)

This column will be named WeekOfMonth. Let me know if this works a little better.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[huskerdon]

scriverb, Thanks for the tip about the Switch function. I'd never used it before, so I wanted to experiment a little more.

AKMonkeyboy, here is a function if you want to take this one step further, instead of just checking for the day of month, where 1-7 = week 1, 8-14 = week 2, etc.

This takes into account the actual "work week" of the month, based on what day the 1st of the month falls on.

Function WeekOfMonth(thedate As Date)

  Dim firstofmonth As Byte, offset As Byte
  Dim strdate As String
  strdate = Month(thedate) & "/1/" & Year(thedate)
  firstofmonth = Weekday(CDate(strdate))
  offset = 9 - firstofmonth
  
  WeekOfMonth = Switch(Day(thedate) < offset, 1, _
  Day(thedate) < (offset + 7), 2, _
  Day(thedate) < (offset + 14), 3, _
  Day(thedate) < (offset + 21), 4, _
  Day(thedate) < (offset + 28), 5, True, 6)
  
  MsgBox WeekOfMonth

[COLOR=green]  ' strdate plugs in "day 1" of the selected month and year

  ' firstofmonth uses the Weekday function to determine
  ' a numeric value for day 1
  ' if Sunday, firstofmonth = 1
  ' if Monday, firstofmonth = 2
  ' etc.
  ' if Saturday, firstofmonth = 7

  ' the offset value = 9 - firstofmonth
  ' for example, if firstofmonth = 1 (Sunday)
  ' then week 1 will include days 1 - 7,
  ' or all days < (9 - 1) 
  '
  ' if firstofmonth = 7 (Saturday)
  ' then week 1 will include day 1 only,
  ' which in this case is the only day < (9 - 7)

  ' instead of hard-coding the days 8, 15, 22, 29,
  ' this method will take the "offset" value for week 1,
  ' and just increment it by 7 for each subsequent week.
  ' This even works for months with 6 partial weeks - 
  ' that is why the "True" portion (the else) of the Switch
  ' function is set to 6, not 5.
  '
  ' (Test this with 11/1/2003 and 11/30/2003 to get a good example)

  ' Of course if you don't want to use Sunday as the first day of the
  ' week, you can adjust the argument in the Weekday function. [/color]

End Function

There may already be a similar built-in function in Excel to do this calculation, but it was fun just to step through the logic of it.

Don

 

[AKMonkeyboy]

Thanks everyone. I haven't had a chance to revisit the problem... but appreciate your help.

Thanks again.

Adam