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 - intWeekdayintWeek1 = intAddOn
intWeek2 = intWeek1 + 7
intWeek3 = intWeek2 + 7
intWeek4 = intWeek3 + 7
intWeek5 = intWeek4 + 7
intWeek6 = intWeek5 + 7If dtmDay <= intWeek6 Then
strWeek = "Week 6"
End IfIf dtmDay <= intWeek5 Then
strWeek = "Week 5"
End IfIf dtmDay <= intWeek4 Then
strWeek = "Week 4"
End IfIf dtmDay <= intWeek3 Then
strWeek = "Week 3"
End IfIf dtmDay <= intWeek2 Then
strWeek = "Week 2"
End IfIf dtmDay <= intWeek1 Then
strWeek = "Week 1"
End IfWscript.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:
Week |
End Date |
6 |
38 |
5 |
31 |
4 |
23 |
3 |
17 |
2 |
10 |
1 |
3 |
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).
very complicated
can u explain this in steps rather than in paragraph.
nice. in excel this is…
if B2 is your date,
=IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))),1,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*1,2,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*2,3,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*3,4,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*4,5,IF(DAY(B2)<=(8-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))+7*5,6))))))
Do we have a PowerShell version?
I have a date of 1/1/15 (Access). I have been able to pull out the month with AccidentMonth: DatePart("m",[Date of Accident]), so now I have a column of number; however, I want to get the months – instead of 1 I want to see January and so on.
Assign the date to a variable so calculated values are from the same timestamp. Use Get-Date to create the variable as a "System.DateTime" type. Now you can use all the fancy tricks and methods with your variable that are available with Get-Date. Use Get-Member
to see the type of $date. i.e. $date | Get-Member
$date = Get-Date "6/30/1998" # or
$date = Get-Date "June 30, 1998" # or
$date = Get-Date
$WeekOfMonth = [math]::Floor((($date).Day-1)/7+1)
$WeekOfMonth
In my previous post I miss understood the request of the blog and I posted the formula to calculate the number of times the day of week has fallen. i.e. 4th Tuesday of the Month, 3rd Thursday of the Month, etc… So today I wanted to share the correct
formula using powershell.
# This formula will give you the week of the month for any date.
# First we need to assign the date with either of the next two lines.
$date = Get-Date # The date and time as of NOW.
$date = Get-Date "6/30/1998" # or an assigned date we choose.
# This is a "TypeName: System.DateTime" variable for the 1st day of the month.
$1stofMonth = $date.AddDays(-($date.Day-1))
# This is the numeric Day of Week value for the 1st day of the month. [0..6]
$dowValue1st = $1stofMonth.DayOfWeek.value__
# We calculate the $WeekOfMonth
$WeekOfMonth = [math]::Ceiling(($date.Day+$dowValue1st-7)/7+1)
$WeekOfMonth
# You can test this using the code next line of code.
1..31 | %{[math]::Ceiling(($_+$dowValue1st-7)/7+1)}
# This formula will give you the week of the month for TODAY’s date using one line of code.
$d = Get-Date ; [math]::Ceiling(($d.Day+(($d.AddDays(-($d.Day-1))).DayOfWeek.value__)-7)/7+1)
I hope this is helpful.