Scripts occasionally need to use dates such as the last day of a month or the last particular weekday (e.g., Friday) in a month. Using VBScript to find these dates isn't a straightforward process because months vary in numbers of days and have a complex relationship with weeks. However, once you understand the behavior of the VBScript date functions, the code is surprisingly short. I'll demonstrate a technique that finds the last day of a month, then extend the method to find the last Friday in a month. When we're done, you'll have a generic function for finding the last particular weekday in a month.
Find the Last Day of a Month
To find the last day of a month, you obviously need to have a particular month in mind, so let's assume you want to determine the last day of the current month. You start by using VBScript's Date() function to get the current date into a variable named thisDay, as this example shows:
thisDay = Date()
Throughout this article, I use data retrieved from running the code on July 15, 2006. To generate similar data on your system, rather than getting the current date, you can use the following line of code to calculate thisDay:
thisDay = DateSerial(2006, 7, 15)
DateSerial is a handy way to unambiguously generate a VBScript date from raw numeric values for the year, month, and day. You can find more information about the DateSerial function in the VBScript help documentation at http://msdn.microsoft.com/library/en-us/script56/html/47ae21c7-193d-4216-a571-7deac1027662.asp.
My date display might look unusual because I use ISO-8601 numeric display on my computer, which means my numeric date display has the format yyyy-mm-dd. Therefore, this line of code
produces the output 2006-07-15. The only information you need from thisDay initially is the year and the month. VBScript provides functions that let you extract specific values for the year, month, and day from a date, so you can get the values for the year and month into variables y and m by using only two more lines of code:
y = Year(thisDay) m = Month(thisDay)
Now, y is 2006, and m is 7.
At this point, even though the script won't reliably know the last day of the specified month, it can find the first day of the following month quite easily. You just need to add 1 to the month and set the day to 1:
Day1 = DateSerial(y, m + 1, 1)
When I run
on my system, the date displays as 2006-08-01. This output doesn't tell you the last day of the month, but it tells you the date of the day immediately following the last day of the month. Therefore, you can probably find the last day of the month by subtracting 1 from this date, but will this method always work?
Suppose you run this script on December 15 instead of July 15. The calculation for Day1 would reduce to this:
Day1 = DateSerial(2006, 12 + 1, 1)
This appears to be wrong because it specifies the 13th month of 2006. However, if you try this nonsense date, the script runs fine; when you echo the date, you'll see 2007-01-01. The DateSerial documentation explicitly mentions this behavior. The value of 13 is a relative date, and VBScript will happily adjust the entire date based on the fact that 12 months is equal to one year. VBScript will even work with 0 as a value: DateSerial (2006, 0, 1) is the same thing as Date-Serial(2005, 12, 1). This behavior holds true for days as well as it does for months, so you can get the last day of the specified month by using this code:
LastDay = DateSerial(y, m + 1, 0)
When you echo this statement, the output will display 2006-07-31. This is a surprisingly simple line of code that always returns a date representing the last day of the month (m) in year (y).
So how do you find the last particular day of a month, such as the last Friday in July of 2006? VBScript understands the concept of weekdays and even has internal constants representing each day of the week. The constants and their values are:
- vbSunday (value of 1)
- vbMonday (value of 2)
- vbTuesday (value of 3)
- vbWednesday (value of 4)
- vbThursday (value of 5)
- vbFriday (value of 6)
- vbSaturday (value of 7)
We can determine the weekday of the last day of the month in VBScript by using the Weekday function on a date, as the following example shows:
wd = Weekday(LastDay)
The variable wd will contain one of the numeric values representing a weekday. For July 2006, which ends on a Monday, the value in wd is 2 (vbMonday).
Finding the Last Friday of the Month
So how do you find the last Friday in the month? The easiest way is to subtract days from LastDay until Weekday(LastDay) is vbFriday. This snippet of code will do the trick:
Do While Weekday(LastDay) _ <> vbFriday LastDay = LastDay - 1 Loop
When the code finishes running, the output for LastDay is 2006-07-28.
The LastVbDay function, which callout A in Listing 1 shows, generalizes this process to find the last occurrence of any specific weekday in any month. You can use the function to get the last Friday of the month, as follows:
LastFriday = _ LastVbDay(thisDay, _ vbFriday)
Using the Code
Now that you have a way to find the last day or last specific weekday in a month, there are some other concerns that you need to consider when using code like this in scripts. These issues exist even if you're using a tool like the Task Scheduler that lets you schedule tasks for the last particular weekday in a month.
The most significant point is that this code tells us about calendar cycles, not work cycles. The most likely reason to use this code is to determine critical dates in an organization's business cycle. However, some of those days will inevitably be dates that aren't working days for a particular organization or individual. In my experience, the most extreme example of this problem happened when I was using NTBackup and the AT service on Windows NT 4.0 Server. The server performed extended backups on one Friday of every month. Over several years, the extended backup Friday fell twice on a standard holiday, twice on a day when the person handling the tapes was out of the office, and once when the organization was closed for a special event. These dates comprised almost 20 percent of the archival backup dates.
We never had a backup failure, however, and it had nothing to do with my scheduling tweaks. The person responsible for handling the tape backups is extremely well organized, and she had previously had a bad experience with a failed backup. When we set up the backup schedule, she worked out every backup date for the next year and made sure that someone was there to insert the proper tape.
If your code depends on some kind of human assistance, the safest solution is to make some out-of-script plans. The simplest way to do this is to look at the effects of the schedule you'll be using. For example, if you're planning to implement a task that will run on the last Friday of every month, you can get a complete list of the last Fridays throughout 2006 and 2007 by downloading and running the code in Listing 1. To find a different last day you can change the vbFriday variable to a different value. You can also use the LastVbDay function in your own scripts to find a particular weekday in a specific month of interest.
Armed with this information, you'll be able to more easily ensure that scheduled monthly tasks will work correctly. The list of dates might jog your memory about a potential problem with the execution schedule, and if necessary, you can enter reminders into a calendar. If you're creating the task schedule for someone else—perhaps a client—the list provides them with the means to set up their own reminders and increases their confidence in the solution you've designed.