Week of the month formula

[frankb]

I need a formula that will return the nth full week of the month that specified date falls in.

Example:
03/31/2006 would be "5" - the fifth full week of March
20/13/2006 would be "2 - the second full week of February

I've tried using a formula that subtracts the week number for the 1st of the month from the week number the date falls into, but it's just not working as I expect it to.

format([ETA],"ww",1) - format(date(format([ETA],"m",1) & "/01/" & format([ETA],"yy",1),"ww",1)

 

[raskew]

Hi -

Check out the Datepart() function.

Bob

 

[frankb]

Sorry, my explanation stunk...

The place my formula is breaking is when I've got a date that falls into a partial week at the start of a month. 02/02/2006 should return "5" for the 5th week of January. I guess that what I need is a way to determine if a date falls into a partial week at the beginning of a month or a full week.

 

[raskew]

Frank -

Think that using week numbers is a risky business, given the variables as to what constitues a full week. Having said that, here's a function that will return the month and week number of a user-supplied date. Built it around the presumption that a week runs Sunday (1) through Saturday (7). The option exists that the week starts on a day other than Sunday, but it's untested in this regard. If you need something different, I'll let you struggle with it for a while.

HTH - Bob

Function fGetWeek(pDate As Date, pStart As Integer) As String
'*******************************************
'Re:        http://www.access-programmers.co.uk/forums/showthread.php?t=103239
'Purpose:   Returns the Month/Full week number of a user-defined date
'Coded by:  raskew
'Inputs:    from debug (immediate) window
'           1) ? fgetWeek(#2/2/06#, 1)
'           2) ? fgetWeek(#2/6/06#, 1)
'Outputs:   1) Jan-5
'           2) Feb-1
'*******************************************

Dim dteHold As Date
Dim dteKeep As Date
Dim blnHold As Boolean

    blnHold = False
    dteHold = pDate
    'determine 1st day of target month
    dteKeep = DateSerial(Year(dteHold), Month(dteHold), 1)
    
    Do While Not blnHold
       'determine first specified pStart of given month, e.g. vbSunday or 1
       dteKeep = dteKeep - WeekDay(dteKeep) + pStart + IIf(WeekDay(dteKeep) > pStart, 7, 0)
    
       If dteKeep <= pDate Then
          blnHold = True
       Else
         'move back to previous month and try again
          dteKeep = DateSerial(Year(dteHold), Month(dteHold) - 1, 1)
       End If
    Loop
    
    fGetWeek = Format(dteKeep, "mmm") & "-" & Int(DateDiff("d", dteKeep, pDate) / 7) + 1
    
End Function