Hey, Scripting Guy! Question

Hey, Scripting Guy! We have a spreadsheet that includes numbers like this: 31738. However, this isn’t supposed to be a number, it’s actually supposed to be a date: 11/22/1986. How can I convert these numbers to dates and then, if needed, convert those dates back to numbers?

-- TC

SpacerHey, Scripting Guy! AnswerScript Center

Hey, TC. Yes, as a matter of fact it did take the Scripting Guy who writes this column nearly an hour to get home the other night; on a typical day that’s a journey that only takes about 25 minutes. Why did it take him so long to get home? That’s easy: as near as he could tell, everyone who lives in the Puget Sound area had left work early and was trying to get home, too. The roads, even the side roads, were absolutely jammed with cars.

Admittedly, some people (we won’t mention any names) were on the road simply so they could get home in time to watch the kickoff of the Rutgers-South Florida football game. However, most people were on the road because they wanted to get home before disaster struck. The forecast for the Seattle area the other night was for winds of up to 70 miles per hour; with memories of last year’s windstorm – and accompanying power outages – fresh in their minds no one was taking any chances. Everyone took off early, the roads were jammed, and the Scripting Guy who writes this column did, indeed, miss the kickoff. As well as a good portion of the first quarter.

Editor’s Note: The Scripting Editor, on the other hand, didn’t panic and wasn’t too concerned about Rutgers and South Florida, and was therefore able to leave work at her usual time. Her typical 35-minute drive took 20 minutes. The Scripting Editor enjoyed this wind storm.

Of course, this Scripting Guy was also late in getting home because he had to stop at the grocery store along the way. The grocery store was also packed with people, and the battery aisle and the potato chip aisle were all-but cleaned out; for example, the couple standing in line in front of the Scripting Guy who writes this column were buying 48 cans of pop and 6 large bags of potato chips. “It’s like people are preparing for the end of the world,” quipped the cashier as the Scripting Guy who writes this column paid for his groceries.

Note to Puget Sound area residents. Admittedly, the Scripting Guys haven’t had a lot of experience with the world coming to an end. (Although, based on the reaction we get from our fellow Microsoft employees, you might think that some of our columns were causing the world to come to an end.) Nevertheless, here’s a tip from the Scripting Guys: if the world really was coming to an end, well, we’re not sure having a huge stockpile of batteries and potato chips is going to help all that much.

Well, OK: maybe having salt-and-vinegar potato chips would help. But other than that ….

As it turns out, it did get windy, and a number of people did lose power, although most of those people had their power restored within a few hours. Compared to last year, when over 1.5 million people lost power, many for days at a time (6 for the Scripting Guy who writes this column), well, the first “big” storm of the year turned out to be much ado about pretty much nothing at all.

But we must admit that something good did come out of all the hoopla and excitement. While he was at the grocery store the Scripting Guy who writes this column noticed that the store was running low on batteries and potato chips; however, this same store was completely out of scripts that could convert an Excel number to a date and then back again. Good heavens! Suppose this had been a real emergency? In an emergency situation, how are people supposed to convert Excel numbers to dates and back again if the grocery stores don’t have an adequate supply of scripts? That’s an unbelievable and inexcusable oversight. (And yes, we do blame the government.) But have no fear; as a public service, the Scripting Guys have written a script that converts a number to a date and back again. And we’re making this script available to the general public at no charge:

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

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1,1) = 31738
Wscript.Echo objExcel.Cells(1,1)

objExcel.Cells(1,1).NumberFormat = "m/d/yyyy"
Wscript.Echo objExcel.Cells(1,1)

objExcel.Cells(1,1).NumberFormat = ""
Wscript.Echo objExcel.Cells(1,1)

Note. Actually we did want to charge the general public for this. But the Scripting Editor, who believes that there are far more important things in life than money and material goods, said we couldn’t.

By the way, did we mention that this same Scripting Editor drives a Lexus?

Before we go any further we should take a second and talk about how Excel works with dates. As far as Excel is concerned, time began on January 1, 1900. (Which is kind of a shame: had they made that January 1, 1899 instead then time would have officially begun on the Scripting Editor’s birthday.) Because time began on January 1, 1900 that means that January 1, 1900 was day 1. In turn, that means that January 2, 1900 was day 2, and October 22, 2007 is day 39377. If you’ve ever imported data into Excel you might have seen dates displayed as numbers. And now you know why: because, as far as Excel is concerned, dates are numbers (serial numbers).

Strange but true.

So how do we convert a date to a number, and vice-versa? Just the way you’d expect us to: by assigning different values to a cell’s NumberFormat property.

And don’t worry; we’ll explain all that in just a moment.

For now, we should point out that our script begins by creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then use these two lines of code to create a new workbook and to bind to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

If you’ve done any scripting at all with Excel then you’ve seen those lines of code a million times. If you haven’t done any scripting at all with Excel, well, now you’ve seen those lines of code one time.

Our next step is to assign the value 31738 to cell A1 (that is, cell row 1, column 1). That’s what we do here:

objExcel.Cells(1,1) = 31738

And then, just to prove that the value of cell A1 really is 31738 we use the following line of code to echo back the cell value:

Wscript.Echo objExcel.Cells(1,1)

And what do we get back when we echo the value of cell A1? Why this, of course:

31738

So far so good, right? Now comes the cool part. How do we convert 31738 to a date? Like this:

objExcel.Cells(1,1).NumberFormat = "m/d/yyyy"

All we’ve done here is change the value of the cell’s NumberFormat property, assigning it the value m/d/yyyy. The NumberFormat property is used to determine how data is displayed in a spreadsheet; here we’re saying that we want this data to be displayed as a date, using the format month (m) / day (d) / year (yyyy). In case you’re wondering, yes, the name “NumberFormat” is something as a misnomer; you can use this property to format values as numbers, as dates, as fractions, as Social Security numbers, as Zip codes…as pretty much anything you want. You can even create custom formats; take a peek at this Hey, Scripting Guy! column for an example.

Note. For more information start up Excel, click Format, then click Cells. (In Excel 2007 click Format on the Home ribbon and select Format Cells.) On the Number tab of the Format Cells dialog box click on the Custom category. See all the formatting symbols shown in the dialog box? All of those symbols can be used in a script to modify the NumberFormat property.

Now look what happens when we echo back the value of cell A1:

11/22/1986

Is that cool or what?

As promised, we can also convert this date back to its serial number equivalent. How? By setting the value of the NumberFormat property to an empty string (the programmatic way to set a cell’s format to General):

objExcel.Cells(1,1).NumberFormat = ""

Will that really turn our date back into a number? Well, let’s echo back the value of cell A1 and see for ourselves:

31738

In other words, we started with cell A1 being equal to 31738 and now, after all this time and effort, we end with cell A1 being equal to 31738. If that’s not a metaphor for the Scripting Guys and their Microsoft careers, well, we don’t know what is.

We hope that answers your question, TC. As for the Scripting Guys, it’s not even lunch time yet, but we’re already making preparations to head for home. Not because we’re concerned about power outages; we just feel like going home. See you tomorrow.

Oh: and could you bring some potato chips, preferably salt-and-vinegar? Thanks.