Hey, Scripting Guy! How Can I Separate The Month From the Year in a Date String Like 122007?

ScriptingGuy1

Hey, Scripting Guy! I have a column of data in Microsoft Excel that is formatted as MYYYY (12008 = January 2008) and MMYYYY (122007 = December 2007). I need to split these values into month and year, but I can’t figure out how to do that. Any suggestions?
— DW

Hey, DW. Well, it’s finally beginning to warm up here in Scripting Land, assuming you can call a high of 52 to be warm. Still, it’s much better than the conditions Scripting Land denizens faced Monday night when the Scripting Son had his latest baseball game; those conditions featured a temperature of 39 degrees Fahrenheit combined with a cold, biting wind. Oh, plus the fact that, shortly before the game began, everyone was treated to a delightful mixture of hail, snow, and rain.

In other words, all things considered, we’ll take a high of 52. And no snow.

Now, admittedly, some of you might be tired of hearing the Scripting Guy who writes this column complain about the weather; after all, in the greater scheme of life, does a trivial thing like the weather really matter all that much? Well, as usual, any time we get asked a deep, philosophical question like that we turn to Scripting Guy Dean Tsaltas for the answer. Here’s what Dean has to say on the subject:

If that doesn’t answer your question, we don’t know what does.

OK, we have to admit that we don’t have any idea what Dean was talking about, either, although we believe he was alluding to the fact that the human body is composed primarily of water, and that water freezes at 32 degrees Fahrenheit. Ergo, you want to stay as far away from 32 degree weather as you can. And, for most people, that’s probably not a bad idea. Fortunately, though, the Scripting Guy who writes this column is not composed primarily of water; he’s composed entirely of fat, which means it will have to get a lot colder (e.g., 2 or 3 degrees below zero) before he has to worry about freezing.

At any rate, the point is – well, you know what? We no longer have any idea what the point is; we’ll have to ask Dean about that. In the meantime, though, here’s a script that can run through an Excel spreadsheet and separate a date like 122007 (December, 2007) into the month (12) and year (2007):

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

i = 1

Do Until objWorksheet.Cells(i,1) = ""
    strDate = objWorksheet.Cells(i,1)
    intLength = Len(strDate)
    intYear = Right(strDate, 4)
    intMonth = Left(strDate, intLength - 4)
    objWorksheet.Cells(i,2) = intMonth
    objWorksheet.Cells(i,3) = intYear
    i = i + 1
Loop

Let’s see if we can figure out how the Scripting Guys decided to tackle this problem. Before we do that, however, let’s take a look at the spreadsheet in question. As you can see, it’s an extremely simple little worksheet, consisting entirely of a series of dates (in the format MonthYear) in column A:

What we need to do here is parse out the month and the year and put those values in separate columns; in other words, we want to end up with a spreadsheet that looks like this:

How are we going to do that? Like we said, let’s see if we can figure that out.

To begin with, we create an instance of the Excel.Application object and set the Visible property to True; that gives us a running instance of Excel that we can see on screen. Once we’ve done that we use these two lines of code to open the file C:\Scripts\Test.xls, and to bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

So are we ready to start parsing dates yet? Be patient; before we can do that we need to set the value of a counter variable named i to 1:

i = 1

We’ll use this counter variable to keep track of our current row within the spreadsheet.

So now are we ready to start parsing dates yet? Oh, no, not even close; we still have to – oh, wait. We mean yes, yes we are ready to start parsing dates.

In order to parse these dates we start off by setting up a Do Until loop that first examines the value of cell row 1, column 1 (cell i, 1):

Do Until objWorksheet.Cells(i,1) = ""

What we’re going to do with this loop is methodically check the cells in column A, row-by-row. As long as the cell has a value the loop will continue; the moment we find a blank cell (that is, a cell with a value equal to an empty string) then the loop will terminate. That’s how we can be sure that we grab – and parse – all the values in column A of our spreadsheet.

Inside the loop we kick things off by snagging the value of cell i, 1 and storing it in a variable named strDate:

strDate = objWorksheet.Cells(i,1)

What does that mean? That means that, the first time through the loop, strDate will be equal to 12007.

That’s great, but we still need to separate the month from the year. To do that, we first use VBScript’s Len function to determine the number of characters in the value:

intLength = Len(strDate)

Why? We’ll explain why in just a second. Before we do that, however, let’s show you how easy it is to identify the year in our date string. This is easy because the year is always the last four characters in the string; in the value 12007 the year is 2007. Because the year is always the last four characters we can use the Right function to grab those four characters and store them in a variable named intYear:

intYear = Right(strDate, 4)

We told you this was easy, didn’t we?

Grabbing the month is a tiny bit trickier. Why? Because we don’t know for sure how many characters are in the month. A month like January will have just a single character (1); a month like October will have two characters (10). Sound hopeless? It’s not:

intMonth = Left(strDate, intLength - 4)

So what are we doing here? Here we’re using the Left function to grab some characters from the beginning of our date string. How many characters are we grabbing? That depends on the length of the string. For example, the value 12007 has a length of 5; that’s because there are five characters in the string. We’ve already determined that the last four characters in the string represent the year; therefore, we want to grab all the characters except the last four. How many that will be? Why, that will be the length of the string minus 4: intLength – 4. With the value 12007 that will be 5 minus 4, or 1; in that case we’ll grab just 1 character from the beginning of the string. If our value is 102007 then we’ll grab two characters. Why? Because the length of the string (6 characters) minus the year (4 characters) equals 2. It’s that easy.

As soon as we have the month stashed away in the variable intMonth and the year tucked away in the variable intYear we can execute these two lines of code:

objWorksheet.Cells(i,2) = intMonth
objWorksheet.Cells(i,3) = intYear

In line 1 we’re simply setting the value of cell B1 (row i, column 2) to the value of intMonth; in line 2 we’re setting the value of cell C1 (row i, column 3) to the value of intYear. Once that’s done we increment the value of our counter variable by 1, then repeat this entire process with the next row in the spreadsheet. Because i will now be equal to 2, that means that, the second time through the loop, we’ll be working with row 2. That’s all we have to do.

All in all this was very easy, wasn’t it?

By the way, it turns out that Dean was right: the human body is composed primarily of water, with the average adult being approximately 70 percent water. For a 150-pound person, that equals 12 gallons or so. In the US tap water can usually be had for less than a penny per gallon; that makes the average American body worth about 12 cents.

Although based on some of the bodies the Scripting Guy who writes this column has seen at his local gym, he believes that price might be a bit high.

ScriptingGuy1

Follow