Hey, Scripting Guy! How can I determine the first Friday in a month?

— JB

Hey, JB. You know, when we first glanced at this question we were hoping it said, “How can I determine if any given day in a month is a Friday?” Why? Well, if that was the case then we had a sure-fire method for you: just walk through the cafeteria in the Scripting Guys building at lunch time. If you walk into the cafeteria at noon on, say, a Monday or a Thursday, the place will be jam-packed; walk through on a Friday, however, and you feel like you’re the last person on Earth. Nobody is around on a Friday.

Of course, that wasn’t your question; instead, you wanted to know how you can identify the first Friday in a month. Admittedly, you might not find the answer to that simply by walking through the Microsoft cafeteria. Instead, you might need to use a script similar to this:

dtmDate = #11/1/2006#

Do Until x = 1
    intDayOfWeek = Weekday(dtmDate)
    If intDayOfWeek = 6 Then
        Wscript.Echo “The first Friday of the month is ” & dtmDate & “.”
        Exit Do
    Else
        dtmDate = dtmDate + 1
    End If
Loop

What’s that? Sure, we can spare a few minutes to explain how this script works. After all, it’s Friday; we pretty much have the place to ourselves.

As you can see, we start out by assigning a date – November 1, 2006 – to a variable named dtmDate:

dtmDate = #11/1/2006#

Oh, right: there’s a very good reason why we set the date to November first. After all, we want to determine the first Friday in the month; therefore we need to start with day 1, just in case the first day in November is a Friday. If it is, great. If it isn’t, then we’ll check to see if the second day in the month is a Friday. And so on. At least that’s the plan; let’s see if it works.

After assigning the date we then set up a Do Until loop that runs until a variable named x is equal to 1. Because x has never been assigned a value, its current value is 0; in fact, the value of x will always be a zero. In other words, we’ve set up a loop designed to run forever. But don’t panic: after all, in the scripting world forever doesn’t always take very long.

Inside the loop the very first thing we do is use the Weekday function to determine the integer value corresponding to the given date:

intDayOfWeek = Weekday(dtmDate)

Depending on the day of the week, the Weekday function will return one of the following values:

Because we’re interested in only Fridays we use this line of code to determine whether or not the Weekday value for 11/1/2006 (the current value of dtmDate) is equal to 6:

If intDayOfWeek = 6 Then

Suppose the Weekday value for 11/1/2006 is 6 (it isn’t, but suppose it is). In that case we echo back the fact that the first Friday in November, 2006 is 11/1/2006 and then call the Exit Do statement to exit our not-so-endless loop:

Wscript.Echo “The first Friday of the month is ” & dtmDate & “.”
Exit Do

That’s nice, but what if the Weekday value for 11/1/2006 isn’t equal to 6 (which it isn’t)? No problem; in that case we simply add 1 day to the date (making the value of dtmDate equal to 11/2/2006) and then loop around and use the Weekday function to test that date:

dtmDate = dtmDate + 1

This process continues until we finally encounter a Friday. When that happens, we’ll get back a message similar to this, we’ll exit the loop, and the script will terminate:

The first Friday of the month is 11/3/2006.

Nice.

By the way, if you want to get a head start on next year, here’s a script (we’ll leave it up to you to figure out exactly how it works) that reports back the first Friday in every month for the year 2007:

For i = 1 to 12
    dtmDate = CDate(i & “/1/2007”)

    Do Until x = 1
        intDayOfWeek = Weekday(dtmDate)
        If intDayOfWeek = 6 Then
            Wscript.Echo “The first Friday of the month is ” & dtmDate & “.”
            Exit Do
        Else
            dtmDate = dtmDate + 1
        End If
    Loop
Next

That’s going to give us back data that looks like this:

The first Friday of the month is 1/5/2007.
The first Friday of the month is 2/2/2007.
The first Friday of the month is 3/2/2007.
The first Friday of the month is 4/6/2007.
The first Friday of the month is 5/4/2007.
The first Friday of the month is 6/1/2007.
The first Friday of the month is 7/6/2007.
The first Friday of the month is 8/3/2007.
The first Friday of the month is 9/7/2007.
The first Friday of the month is 10/5/2007.
The first Friday of the month is 11/2/2007.
The first Friday of the month is 12/7/2007.

The secret here is that we’ve added a second loop. We want to report data back for 12 months, so we set up a For Next loop that runs from 1 through 12. Then, we use the counter variable for that loop (which we named i) in a little formula that determines the first day in each month:

dtmDate = CDate(i & “/1/2007”)

As you can see, when i is equal to 1 we’ll be assigning dtmDate the value 1/1/2007; when i is equal to 2 (which it will be the second time through the loop) then we’ll be assigning dtmDate the value 2/1/2007. Etc.

One last thing. If the Scripting Editor tries to slip a note in here saying that this column exaggerates the number of people who skip … lunch … on a Friday, well, look at it this way. Who are you going to trust when it comes to being lazy and weaseling out work: the Scripting Editor, or the Scripting Guy who writes this column? (Or, to put it a little more accurately, the Scripting Guy who writes this column if he doesn’t have to coach baseball or if he isn’t taking a week off or ….)

That’s what we thought.