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

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

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


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

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!

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.


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

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?

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.


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