Custom function for weeknum to Month i got some issues with this need alittle help

[robertjtucker]

Godd Morning all,
I have been trying to write this function, it is looking at a date converting it to a weeknumber. Then I want it to select the month, from the weeknumber. The only reason I need it this way is because we use a by week calendar, for example Jan 06 ended on Feb the 4th, because our month does not end in the middle of a week only ant the end. So some one help straighten me out here. Oh and the reason that I have the select picking a serial number is because I want the format to read Apr-06. Hope some one understand where I am getting at.
Thanks, Bob

Function WKnum(d1 As Date) As Long

Dim Mnth1 As Long
Dim Today As Date

Today = Now - 1
If d1 < Today Then
GoTo late1
If d1 > 39083 Then 'Later than 01/01/2007
GoTo Unsch
'Else: GoTo Sc

Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
WKnum = ((d1 - d2 + Weekday(d2) + 5) / 7)
'Exit Function
'If d1 < Today Then
' GoTo late1
'If d1 > 39083 Then 'Later than 01/01/2007
' GoTo Unsch
'Else: GoTo Sc

'Sc:
Select Case WKnum
Case 1 To 5: Mnth1 = 38732

Case 6 To 9: Mnth1 = 38763

Case 10 To 13: Mnth1 = 38791

Case 14 To 18: Mnth1 = 38822

Case 19 To 22: Mnth1 = 38852

Case 23 To 26: Mnth1 = 38883

Case 27 To 31: Mnth1 = 38913

Case 32 To 35: Mnth1 = 38944

Case 36 To 39: Mnth1 = 38975

Case 40 To 44: Mnth1 = 39005

Case 45 To 48: Mnth1 = 39036

Case 49 To 52: Mnth1 = 39066

Case Else
'WKnum = 0
End Select
WKnum = Mnth1

late1:
WKnum = "Late"

Unsch:
WKnum = "Unscheduled"

End If
End If
End Function

[[email protected]]

If it's a function, then presumably it takes in the date, so
MONTH(InputDate) would give you the month you need.

[robertjtucker]

Yes it would but as I stated above I do not use a traditional monthly calendar I use a weekly calendar meaning that My April started 04/02/06 and ends on05/06/06. Because my mfg months do not end in the middle of a week only on saturdays. So saying that if the date in the cell says 05/04/2006 then that is April because the 30th is on Sunday and sunday is the beginning of the weekso April has to end on the end of the week 05/06/2006. So yes that function would work for traditional calendars I don't use that calendar in my business. Thanks for your help though,
Bob

[[email protected]]

so what about Inputday-mod(Weekday(inputDay),7) which would take you
back to a Saturday - the MOD being there to ensure you leave Saturdays
alone!

[robertjtucker]

That just gives me the number of the day of the week, that is not what I am looking for. That is why I was writting the function weeknum(1-5) =Jan-2006.

[[email protected]]

No it doesn't

Thursday May 04, 2006 translates to Saturday April 29, 2006
Friday May 05, 2006 to Saturday April 29, 2006
Saturday May 06, 2006 to Saturday May 06, 2006
Sunday May 07, 2006 to Saturday May 06, 2006
Monday May 08, 2006 to Saturday May 06, 2006
etc

so you can then get the month from this surely?

[robertjtucker]

No when I used you formula on 12/08/2006 it gave me a 6 that is a Friday the 6th day of the week. Anyway it doesn't matter this will not work becuase

Sunday May 07, 2006 to Saturday May 06, 2006
Monday May 08, 2006 to Saturday May 06, 2006

Are not the same month on my calendar 05/07/06 is May 05/06/06 is April becuase 05/07/2006 is the first sunday in May it begins May on my calendar that is why I am going by week numbers my months go by week numbers that is why I was writting the function.