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


--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805
View this thread: http://www.excelforum.com/showthread...hreadid=537189