Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function for weeknum to Month i got some issues with this need alittle help
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weeknum function | Excel Discussion (Misc queries) | |||
weeknum for month | Excel Worksheet Functions | |||
weeknum of the month | Setting up and Configuration of Excel | |||
weeknum of the month | Setting up and Configuration of Excel | |||
Custom Format for WEEKNUM(Today()) | Excel Discussion (Misc queries) |