{"id":55853,"date":"2008-04-03T15:39:00","date_gmt":"2008-04-03T15:39:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/04\/03\/hey-scripting-guy-how-can-i-separate-the-month-from-the-year-in-a-date-string-like-122007\/"},"modified":"2008-04-03T15:39:00","modified_gmt":"2008-04-03T15:39:00","slug":"hey-scripting-guy-how-can-i-separate-the-month-from-the-year-in-a-date-string-like-122007","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-separate-the-month-from-the-year-in-a-date-string-like-122007\/","title":{"rendered":"Hey, Scripting Guy! How Can I Separate The Month From the Year in a Date String Like 122007?"},"content":{"rendered":"

\"Hey, <\/h2>\n

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\u2019t figure out how to do that. Any suggestions?
— DW<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, DW. Well, it\u2019s finally beginning to warm up here in Scripting Land, assuming you can call a high of 52 to be warm. Still, it\u2019s 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. <\/p>\n

In other words, all things considered, we\u2019ll take a high of 52. And no snow.<\/p>\n

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<\/i> 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\u2019s what Dean has to say on the subject:<\/p>\n\n<\/thead>\n\n\n
\n

\u201cWhen it\u2019s nearly cold-enough to change the physical state of the primary constituent of your consciousness carrier you cannot call it a beautiful day!\u201d<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

If that doesn\u2019t answer your question, we don\u2019t know what does.<\/p>\n

OK, we have to admit that we don\u2019t have any idea what Dean was talking about, either, although we believe<\/i> 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\u2019s probably not a bad idea. Fortunately, though, the Scripting Guy who writes this column is not<\/i> composed primarily of water; he\u2019s composed entirely of fat, which means it will have to get a lot<\/i> colder (e.g., 2 or 3 degrees below zero) before he has to worry about freezing.<\/p>\n\n<\/thead>\n\n\n
\n

Note<\/b>. OK, that\u2019s not true: the Scripting Guy who writes this column isn\u2019t composed entirely<\/i> of fat; he\u2019s about 50 percent fat and 50 percent tattoos, including the tattoo of the battleship U.S.S. Missouri emblazoned across his chest.<\/p>\n

OK, so that\u2019s not true, either: the Scripting Guy who writes this column doesn\u2019t have any<\/i> tattoos, let alone a tattoo of the battleship U.S.S. Missouri emblazoned across his chest. Besides, if he was going to get a tattoo of a ship emblazoned anywhere it would be across his belly; that way he\u2019d have room for the entire Spanish armada. Which would make for a pretty impressive tattoo when you think about it.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

At any rate, the point is \u2013 well, you know what? We no longer have any idea what the point is; we\u2019ll have to ask Dean about that. In the meantime, though, here\u2019s a script that can run through an Excel spreadsheet and separate a date like 122007 (December, 2007) into the month (12) and year (2007):<\/p>\n

Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n\ni = 1\n\nDo Until objWorksheet.Cells(i,1) = \"\"\n    strDate = objWorksheet.Cells(i,1)\n    intLength = Len(strDate)\n    intYear = Right(strDate, 4)\n    intMonth = Left(strDate, intLength - 4)\n    objWorksheet.Cells(i,2) = intMonth\n    objWorksheet.Cells(i,3) = intYear\n    i = i + 1\nLoop\n<\/pre>\n
Let\u2019s see if we can figure out how the Scripting Guys decided to tackle this problem. Before we do that, however, let\u2019s take a look at the spreadsheet in question. As you can see, it\u2019s an extremely simple little worksheet, consisting entirely of a series of dates (in the format MonthYear) in column A:<\/p>\n
\"Microsoft <\/p>\n
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:<\/p>\n
\"Microsoft <\/p>\n
How are we going to do that? Like we said, let\u2019s see if we can figure that out.<\/p>\n
To begin with, we create an instance of the Excel.Application<\/b> object and set the Visible<\/b> property to True; that gives us a running instance of Excel that we can see on screen. Once we\u2019ve 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:<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/pre>\n
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:<\/p>\n
i = 1\n<\/pre>\n
We\u2019ll use this counter variable to keep track of our current row within the spreadsheet.<\/p>\n
So now<\/i> are we ready to start parsing dates yet? Oh, no, not even close; we still have to \u2013 oh, wait. We mean yes, yes we are<\/i> ready to start parsing dates.<\/p>\n
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):<\/p>\n
Do Until objWorksheet.Cells(i,1) = \"\"\n<\/pre>\n
What we\u2019re 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\u2019s how we can be sure that we grab \u2013 and parse \u2013 all the values in column A of our spreadsheet.<\/p>\n
Inside the loop we kick things off by snagging the value of cell i, 1 and storing it in a variable named strDate:<\/p>\n
strDate = objWorksheet.Cells(i,1)\n<\/pre>\n
What does that mean? That means that, the first time through the loop, strDate will be equal to 12007.<\/p>\n
That\u2019s great, but we still need to separate the month from the year. To do that<\/i>, we first use VBScript\u2019s Len<\/b> function to determine the number of characters in the value:<\/p>\n
intLength = Len(strDate)\n<\/pre>\n
Why? We\u2019ll explain why in just a second. Before we do that, however, let\u2019s 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<\/b> the year is 2007. Because the year is always the last four characters we can use the Right<\/b> function to grab those four characters and store them in a variable named intYear:<\/p>\n
intYear = Right(strDate, 4)\n<\/pre>\n
We told you this was easy, didn\u2019t we?<\/p>\n
Grabbing the month is a tiny bit trickier. Why? Because we don\u2019t 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\u2019s not:<\/p>\n
intMonth = Left(strDate, intLength - 4)\n<\/pre>\n
So what are we doing here? Here we\u2019re using the Left<\/b> function to grab some characters from the beginning<\/i> 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\u2019s because there are five characters in the string. We\u2019ve already determined that the last four characters in the string represent the year; therefore, we want to grab all the characters except<\/i> the last four. How many that will be? Why, that will be the length of the string minus 4: intLength \u2013 4<\/b>. With the value 12007 that will be 5 minus 4, or 1; in that case we\u2019ll grab just 1 character from the beginning of the string. If our value is 102007 then we\u2019ll grab two<\/i> characters. Why? Because the length of the string (6 characters) minus the year (4 characters) equals 2. It\u2019s that easy.<\/p>\n
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:<\/p>\n
objWorksheet.Cells(i,2) = intMonth\nobjWorksheet.Cells(i,3) = intYear\n<\/pre>\n
In line 1 we\u2019re simply setting the value of cell B1 (row i, column 2) to the value of intMonth; in line 2 we\u2019re setting the value of cell C1 (row i, column 3) to the value of intYear. Once that\u2019s 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\u2019ll be working with row 2. That\u2019s all we have to do.<\/p>\n
All in all this was very<\/i> easy, wasn\u2019t it?<\/p>\n
By the way, it turns out that Dean was right: the human body is<\/i> 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. <\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t figure out how to do that. Any suggestions?— DW Hey, DW. Well, it\u2019s finally beginning […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,48,49,3,4,21,5],"class_list":["post-55853","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-string-manipulation","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019t figure out how to do that. Any suggestions?— DW Hey, DW. Well, it\u2019s finally beginning […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55853","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55853"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55853\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55853"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}