How Can I Determine Last Month’s Date?

ScriptingGuy1

Hey, Scripting Guy! How can I determine last month’s date? I need to be able to take the month and year for the previous month (using the format mmyy) and create a file name like this: test0606.xls.

— DR

Hey, DR. We have to admit that, at first, this question posed a bit of a problem for the Scripting Guys. Not because this is an especially tough script to write; it isn’t. It’s just that we never have to bother with scripts to find out about things that happened in the past: if we want old and out-of-date information we just go ask Greg. If you ever need information about something no else cares about anymore, trust us: Greg’s the guy to see.

On the other hand, Greg always seems to leave early to coach baseball, and he adamantly refuses to make house calls; that means there’s little chance he’ll drop by and help you determine last month’s date. (Granted, no one has ever actually asked him to make a house call, but he still refuses out of general principle.) So if Greg won’t help us are we out of luck here? Of course not; as it turns out, Greg can be more-than-adequately replaced by just a few lines of VBScript code:

intMonth = Month(Date)
intYear = Year(Date)

If intMonth = 1 Then
    intMonth = 12
    intYear = intYear – 1
Else
    intMonth = intMonth – 1
End If
If intMonth < 10 Then
    intMonth = “0” & intMonth
End If
intYear = Right(intYear, 2)
strFileName = “test” & intMonth & intYear & “.xls”
Wscript.Echo strFileName

As you can see, determining the current month and the current year is pretty easy: we just need to use the Month and Year functions, respectively:

intMonth = Month(Date)
intYear = Year(Date)

Those two lines of code are going to give us back values like 7 and 2006, assuming that we’re currently in July, 2006. And, as usual, you’re way ahead of us: if the current month is equal to 7, then all we have to do is subtract 1 from the current month and then we’ll know the previous month: month number 6 (June). Case closed!

Or is it? As Greg would be the first to point out, there’s a problem with that approach. Suppose we run our script on January 3, 2006. January is month 1; if we subtract 1 from 1 we get … um, month 0. On top of that, the month prior to January 2006 is December 2005, but we still have 2006 as the year. Uh-oh.

Wait, put down the phone; there’s no need to call Greg. Here’s how we can overcome that little obstacle:

If intMonth = 1 Then
    intMonth = 12
    intYear = intYear – 1
Else
    intMonth = intMonth – 1
End If

All we’re doing here is setting up an If-Then statement to check the value of intMonth. If intMonth is equal to 1 (which will be the case if we run the script in January) we use these two lines of code to set the month to 12 (December) and the year to the current year minus 1 (e.g., 2006 – 1, or 2005):

intMonth = 12
intYear = intYear – 1

If intMonth is equal to anything other than 1 then we simply assign the variable a new value: the current value (e.g., 7) minus 1. We’re now able to correctly determine the previous month and year, even if we happen to run the script in January.

Oh: right. Greg (who’s now feeling a bit miffed because we don’t need his help) has just pointed out another problem. DR wants to use the format mmyy; that is, two digits for the month and two digits for the year. In other words, he wants 06 and 06, and we currently have 6 and 2006. Now is it time to go crawling back to Greg, begging for his help?

Listen, we don’t need Greg’s help in order to turn 6 2006 into 06 06. For starters, let’s take a look at a block of code that will turn 6 into 06:

If intMonth < 10 Then
    intMonth = “0” & intMonth
End If

Nothing to it, right? Here we’re simply checking to see if the value of intMonth is less than 10. Why 10? Well, if intMonth is equal to 10, 11, or 12, then there’s no problem; in that case the month already has two digits. We’re only concerned about months 1 through 9, the single-digit months. If we’re dealing with one of those months we just use this line of code to place a leading zero before the month number:

intMonth = “0” & intMonth

Just like that intMonth changes from 6 to 06. Which is just what we wanted it to do.

Converting 2006 to 06 is even easier; to handle that problem we just use the Right function to grab the two right-most characters off the end of 2006:

intYear = Right(intYear, 2)

Once that line of code executes the variable intMonth will be equal to 06 and the variable intYear will be equal to 06. That means we can then use this line of code to piece together our file name:

strFileName = “test” & intMonth & intYear & “.xls”

And that’s about it. To verify that the process worked we finish the script by echoing back the new file name:

test0606.xls

Cool. And we didn’t need Greg’s help at all.

ScriptingGuy1

Follow