Nth Week of the Month

[jmerencilla]

hi. given a certain date (say, May 4, 2003), i want to know in what week number of the month it falls under. my function should return 2nd week of May. would you guys know how to do it? please help. thanks.

-jun

 

[MichaelRed]

First, define the WEEK (or more specifically) what denotes the start / end for your week. Then, count the # of occurances of that since the 1st,

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[FRoeCassNet]

Basically get a value to offset the day of month by. Find the day of the week that the first of the month falls on and subtract that from 8 (8th day is start of 2nd week). Then it's just a simple algorithm to get which week the day falls on. As MichaelRed said though, you need to adjust the Weekday() for which day you use as the beginning of the week.

Dim dVal As Date
Dim nOff As Integer

dVal = #5/1/2003#
nOff = 8 - Weekday(dVal, vbSunday)
For dVal = #5/1/2003# To #5/31/2003#
Debug.Print dVal & " is in week #" & Int((day(dVal) + nOff) / 7) + 1
Next

 

[jmerencilla]

thanks MichaelRed. but honestly, i dont get your idea. can you give me some sample code to fast track me on this one? as i understand, you want me to create an enum list which contains the weeks. the problem with this is any date can be provided. doing so will create a huge list of week, which is also difficult to do. with my example (May 4, 2003) you'll notice that it starts the beginning of the 2nd week of may (i define which weekday starts the week) and my function should return 2. if the input is May 4 2002, it should return 1. my problem is i cannot find any algorithm/functions that will do this for me. thanks again for the help.

 

[strongm]

>my problem is i cannot find any algorithm/functions that will do this for me.

Then do a Keyword search in this forum. We've provided a whole host of varying functions in here for working with dates like this...

 

[jmerencilla]

thanks FRoeCassNet. i copied your code but it gives me this error 'For' loop control variable cannot be of type 'Date'. any other suggestions?

 

[jmerencilla]

thanks strongm. i already did that. unfortunately, i found none. though i found some codes but i cannot combine them to arrive at my desired output.

 

[strongm]

Private Function WeekOfMonth(dDate As Date, Optional FirstDayOfWeek As VbDayOfWeek = vbSunday)
Dim FirstOfMonth As Date

' Step one: get 1st of Month
FirstOfMonth = Format(dDate, "1/mm/yyyy&quot
' Step two: count weeks between 1st of month and selected date
WeekOfMonth = DateDiff("ww", FirstOfMonth, dDate) + 1
End Function

 

[BlackburnKL]

Assuming that you are referring to calendar weeks (Sunday being the first day of the weeks as it appears on standard calendars), you can determine which week any given date falls in using the following code:

Public Function Week(ByVal InputDate as Date) As Integer
Week = 2+Int((Day(InputDate)-1-Weekday(InputDate))/7)
End Function

Explanation:
This is simplified code, without any error handling. It assumes that you pass it a valid date.

The Int() function in VB6 returns the integer portion of a number (the next lower integer). Therefore, Int(3.1) returns 3. For negative numbers, it works the same, but gives what is probably an unexpected result. Int(-3.1) returns -4 (the next lower integer). However, this works precisely the way we want it to for this calculation.

Day(InputDate) returns the day of the month (4 in the example of 4 May 2003).

Weekday(InputDate) returns the day of the week (based on your computer settings, with Sunday = 1 by default). You can force the first day of the week to be what you want, but to keep the example simple, I didn't. In the example of 4 May 2003, it would return vbSunday which = 1 by default.

Any date is at least in the first week of the month. Any date that is larger than the day of the week is in at least the second week of the month. Therefore, by using week 2 as a base and using the Int() capabilities as described above (after dividing by 7 days per week), I subtract 1 from 2 for all dates where Day(InputDate) <= Weekday(InputDate). Subtracting 1 from Day(InputDate) takes care of the = portion.

Anyway, I haven't actually run the code, but put it through the paces. I think it will provide what you asked.

Good luck. BlackburnKL

 

[MichaelRed]

I do not recall generating (or suggesting the generation of) any enum - or any other structures. The process is simplistic and adequeatly (if somewhat inefficiently) outlined within this thread, and (as noted by strongm oft discussed within Tek-Tips. Although the search facility here is (in MY opinion) woeful, it should return a multitude of responses using dateadd, datediff or dateserial, which are the more common methods used in the calculation. I, at least, attempt to view the participants of these fora as &quot;professionals&quot;, and in that regard hesitate to suggest that one refer to the simple help on various topics within VB. However it would appear that such advice is at least useful in this instance.

Per my initial post, the only 'issue' or question relevant is your app's definition of the start (or end) of the week. Given that information and a date, is should be relatively obvious as to the generation of the start of the first week of any month. From that point, the week of any date within the month is also somewhat transparently derived (number of intervals of &quot;7&quot; between the start of hte first week and the givne date? with some error checking).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[strongm]

They do keep promising to improve the search facility...

 

[MichaelRed]

Something alog the lines of wishing in one hand SOMETHING ELSE in the other ... which one gets full?

On the other hand, you did not implement the FirstDayOfWeek argument within the procedure, and the date format instruction should (probably) be in the standard U.S. format?

Then again, the other 'soloution' doesn't even provide the argumant prototype ...

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[strongm]

Oops, yes, did miss out the FirstDayOfWeek bit. Here's the corrected code:
[tt]
Private Function WeekOfMonth(dDate As Date, Optional FirstDayOfWeek As VbDayOfWeek = vbSunday)
Dim FirstOfMonth As Date

' Step one: get 1st of Month
FirstOfMonth = Format(dDate, &quot;1/mm/yyyy&quot
' Step two: count weeks between 1st of month and selected date
WeekOfMonth = DateDiff(&quot;ww&quot;, FirstOfMonth, dDate, FirstDayOfWeek) + 1
End Function
[/tt]
Standard US format? Why would that be necessary? Particularly since I'm in the UK...

 

[jmerencilla]

thanks guys. i appreciate the help. now if you'll excuse me, i need some codes test

'till next time

Jun E. Merencilla, MCP

 

[MichaelRed]

Standard US format? Why would that be necessary? Particularly since I'm in the UK...[/]

perhaps at least a comment then re the local settings and their influence on the results???

Teh &quot;UK&quot; may be the father land of the ENGLISH language, not the PROGRAMMING lannguage some of us neophytes can become confused ... debugging / troubleshooting not being a particularly glamorous undertaking , , ,


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[strongm]

But it makes no difference, Michael. As long as VB recognises the results of the Format command as a date it casts it on the fly to the correct internal representation, which is all we need for this function.

The following should demonstrate my point, as it will display the date (or should) in the correct short format for your regional settings, no matter that the Format command is using a slightly odd UK configuration:

Dim RandomDate As Date
RandomDate = Format(Now(), &quot;dd, mm, yyyy&quot
MsgBox RandomDate