Hey, Scripting Guy! How can I determine the week of the month a date falls in?

— AK

Hey, AK. You’re probably familiar with the book Moby Dick, the story of a crazy sea captain who became obsessed with hunting down and finishing off the great white whale. Well, this question turned out to be the Moby Dick of the scripting world. When we first read it we turned to the VBScript documentation, assuming we’d find the WeekOfTheMonth function in there. We didn’t: no such function exists. We then did a cursory search of the newsgroups and couldn’t find an answer there either. And then we became absolutely obsessed with figuring out how you can determine the week of the month a date falls in.

Well, sort of obsessed: we didn’t actually do anything about it, although every now and then we’d think, “Man, we should try to figure out that week of the month thing.” And then finally, a couple days ago, we sat down and tried to come up with a solution.

And guess what: we came up with one. We’re not claiming this is a particularly elegant solution, and we have no doubt that there are better and more efficient ways of solving this problem. But hey, it works, and that’s really the bottom-line, right? (As you might expect, it’s always us less-than-elegant types who argue that elegance doesn’t really matter.)

Here’s a script that will tell you the week of the month that December 19, 2005 falls in:

dtmTargetDate = #12/19/2005#   

dtmDay = DatePart(“d”, dtmTargetDate)
dtmMonth = DatePart(“m”, dtmTargetDate)
dtmYear = DatePart(“yyyy”, dtmTargetDate)
dtmStartDate = dtmMonth & “/1/” & dtmYear
dtmStartDate = CDate(dtmStartDate)
intWeekday = Weekday(dtmStartDate)
intAddon = 8 – intWeekday
intWeek1 = intAddOn
intWeek2 = intWeek1 + 7
intWeek3 = intWeek2 + 7
intWeek4 = intWeek3 + 7
intWeek5 = intWeek4 + 7
intWeek6 = intWeek5 + 7
If dtmDay <= intWeek6 Then
    strWeek = “Week 6”
End If
If dtmDay <= intWeek5 Then
    strWeek = “Week 5”
End If
If dtmDay <= intWeek4 Then
    strWeek = “Week 4”
End If
If dtmDay <= intWeek3 Then
    strWeek = “Week 3”
End If
If dtmDay <= intWeek2 Then
    strWeek = “Week 2”
End If
If dtmDay <= intWeek1 Then
    strWeek = “Week 1”
End If
Wscript.Echo strWeek

This is a semi-complicated script so we only have room in this column to provide an overview of how it works; if you want the gory details you’ll have to sort them out for yourself. We should also note that this script assumes that the first week in the month is whatever week day 1 falls in; we’re not interested in the first full week of the month or the first week with a workday in it or anything like that. If you want to calculate weeks like that, well, you’re on your own there, too.

But enough about that. The script itself is built on the assumption that the key to determining the week of the month that a date falls in is this: determining the day of the week for the first day of the month. Why? Well, suppose day 1 falls on a Saturday. That means day 2 falls on a Sunday which – for our purposes – would mean that day 2 occurs in week 2. But suppose day 1 falls on a Friday? In that case, day 2 falls on a Saturday which – again, for our purposes – would mean that day 2 falls in week 1.

Confused? Take a look at the calendar for December 2005. We’re saying that December 1, 2, and 3 fall in week 1; December 4 marks the first day of week 2:

From this picture we know that our date – December 19, 2005 – falls in week 4. But how do we determine that programmatically?

We begin by using VBScript’s DatePart function to extract the day (d), month (m), and year (yyyy) from the date:

dtmDay = DatePart(“d”, dtmTargetDate)
dtmMonth = DatePart(“m”, dtmTargetDate)
dtmYear = DatePart(“yyyy”, dtmTargetDate)

We then construct a new date representing December 1, 2005 using this code:

dtmStartDate = dtmMonth & “/1/” & dtmYear
dtmStartDate = CDate(dtmStartDate)

In the first line we put together the date string – 12/1/2005 – and in the second line we use the CDate function to ensure that VBScript treats the string as a date-time value. That’s not too bad, is it?

But now it gets a little crazy. We begin by using the Weekday function to determine the day of the week for December 1^st:

intWeekday = Weekday(dtmStartDate)

Weekday returns an integer value ranging from 1 (Sunday) to 7 (Saturday). Because December 1^st falls on a Thursday we get back a 5.

Next we need to determine which date represents the last day in week 1; that’s important because once we know that we can calculate the end dates for each week. And once we know that we can figure out which week any given date falls in. To determine the end date for week 1 we use this code:

intAddon = 8 – intWeekday

Why do we use that code? Good question. If you look at the calendar, December 1 occurs on a Thursday, which has an integer value of 5. If we subtract 5 from 8 we get 3. And take another look at the calendar: December 3^rd just happens to be the last day of week 1. Turns out that all we have to do is subtract the Weekday value from 8 and we’ll know the date for the last day of week 1.

Yeah, we know. But play around with it a bit and you’ll see that it works.

Now that we know that week 1 ends on December 3^rd we can easily calculate the end dates for every other week; after all, week 2 will end on December 3^rd plus 7 days, or December 10^th. Because a month can have as many as six weeks we go ahead and calculate end dates for six weeks (the fact that most months won’t have six weeks doesn’t matter):

intWeek1 = intAddOn
intWeek2 = intWeek1 + 7
intWeek3 = intWeek2 + 7
intWeek4 = intWeek3 + 7
intWeek5 = intWeek4 + 7
intWeek6 = intWeek5 + 7

This next part we could have done in a few less lines of code, but we wanted to make it clear what we’re doing; therefore we put the code together using a bunch of If-Then statements. We’ve already determined the day part of our target date: 19. And now we’ve determined the end date for each week in the month:

Our next step is to see if our day – 19 – is less than or equal to the end date for the various weeks. Is 19 less than the week 6 end date of 38? Yes, so we set the value of the variable strWeek to “Week 6”. Is it less than the week 5 end date of 31? Yes, so we now set the value of strWeek to “Week 5”. How about week 4? Yes, so now strWeek equals “Week 4”. Make sense?

Ok, so what about week 3? Is 19 less than or equal to 17? No, so we don’t change the value of strWeek; we leave it alone. When we finish the last of our If-Then statements we echo the results: turns out that December 19, 2005 falls in week 4 of the month.

Again, a cursory explanation for a semi-complicated script and we apologize for that. But at least the script works. And as crazy as the script might seem, it was still a heck of a lot easier than trying to hunt down and kill a great white whale (even when it comes to our obsessions we try to take the easiest possible route).