{"id":63753,"date":"2007-10-22T23:21:00","date_gmt":"2007-10-22T23:21:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/10\/22\/hey-scripting-guy-how-can-i-convert-a-number-to-a-date-and-then-back-in-excel\/"},"modified":"2007-10-22T23:21:00","modified_gmt":"2007-10-22T23:21:00","slug":"hey-scripting-guy-how-can-i-convert-a-number-to-a-date-and-then-back-in-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-convert-a-number-to-a-date-and-then-back-in-excel\/","title":{"rendered":"Hey, Scripting Guy! How Can I Convert a Number to a Date and Then Back in Excel?"},"content":{"rendered":"

\"Hey, <\/H2>\n

Hey, Scripting Guy! We have a spreadsheet that includes numbers like this: 31738. However, this isn\u2019t supposed to be a number, it\u2019s 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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, TC. Yes, as a matter of fact it did<\/I> take the Scripting Guy who writes this column nearly an hour to get home the other night; on a typical day that\u2019s a journey that only takes about 25 minutes. Why did it take him so long to get home? That\u2019s 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.<\/P>\n

Admittedly, some people (we won\u2019t 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\u2019s windstorm \u2013 and accompanying power outages<\/B><\/A> \u2013 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Ed<\/B>itor\u2019s Note<\/B>: The Scripting Editor, on the other hand, didn\u2019t panic and wasn\u2019t 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<\/I> wind storm.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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. \u201cIt\u2019s like people are preparing for the end of the world,\u201d quipped the cashier as the Scripting Guy who writes this column paid for his groceries.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note to <\/B>Puget Sound<\/B> area residents<\/B>. Admittedly, the Scripting Guys haven\u2019t 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<\/I> the world to come to an end.) Nevertheless, here\u2019s a tip from the Scripting Guys: if the world really was<\/I> coming to an end, well, we\u2019re not sure having a huge stockpile of batteries and potato chips is going to help all that much.<\/P>\n

Well, OK: maybe having salt-and-vinegar potato chips would help. But other than that \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

As it turns out, it did<\/I> get windy, and a number of people did<\/I> 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 \u201cbig\u201d storm of the year turned out to be much ado about pretty much nothing at all.<\/P>\n

But we must admit that something good did<\/I> 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<\/I> scripts that could convert an Excel number to a date and then back again. Good heavens! Suppose this had been a real<\/I> emergency? In an emergency situation, how are people supposed to convert Excel numbers to dates and back again if the grocery stores don\u2019t have an adequate supply of scripts? That\u2019s an unbelievable and inexcusable oversight. (And yes, we do<\/I> 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\u2019re making this script available to the general public at no charge:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objExcel.Cells(1,1) = 31738\nWscript.Echo objExcel.Cells(1,1)<\/p>\n
objExcel.Cells(1,1).NumberFormat = “m\/d\/yyyy”\nWscript.Echo objExcel.Cells(1,1)<\/p>\n
objExcel.Cells(1,1).NumberFormat = “”\nWscript.Echo objExcel.Cells(1,1)\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Actually we did<\/I> 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\u2019t.<\/P>\n
By the way, did we mention that this same Scripting Editor drives a Lexus<\/I>?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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\u2019s 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\u2019ve 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<\/I> numbers (serial numbers).<\/P>\n
Strange but true.<\/P>\n
So how do we convert a date to a number, and vice-versa? Just the way you\u2019d expect us to: by assigning different values to a cell\u2019s NumberFormat<\/B> property.<\/P>\n
And don\u2019t worry; we\u2019ll explain all that in just a moment.<\/P>\n
For now, we should point out that our script begins by creating an instance of the Excel.Application<\/B> object and then setting the Visible<\/B> 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
If you\u2019ve done any scripting at all with Excel then you\u2019ve seen those lines of code a million times. If you haven\u2019t done any scripting at all with Excel, well, now you\u2019ve seen those lines of code one time.<\/P>\n
Our next step is to assign the value 31738 to cell A1 (that is, cell row 1, column 1). That\u2019s what we do here:<\/P>
objExcel.Cells(1,1) = 31738\n<\/PRE>\n
And then, just to prove that the value of cell A1 really is<\/I> 31738 we use the following line of code to echo back the cell value:<\/P>
Wscript.Echo objExcel.Cells(1,1)\n<\/PRE>\n
And what do we get back when we echo the value of cell A1? Why this, of course:<\/P>
31738\n<\/PRE>\n
So far so good, right? Now comes the cool part. How do we convert 31738 to a date? Like this:<\/P>
objExcel.Cells(1,1).NumberFormat = “m\/d\/yyyy”\n<\/PRE>\n
All we\u2019ve done here is change the value of the cell\u2019s NumberFormat property, assigning it the value m\/d\/yyyy<\/B>. The NumberFormat property is used to determine how data is displayed in a spreadsheet; here we\u2019re saying that we want this data to be displayed as a date, using the format month (m<\/B>) \/ day (d<\/B>) \/ year (yyyy<\/B>). In case you\u2019re wondering, yes, the name \u201cNumberFormat\u201d 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\u2026as pretty much anything you want. You can even create custom formats; take a peek at this <\/B>Hey, Scripting Guy!<\/I><\/B> column<\/B><\/A> for an example.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. For more information start up Excel, click Format<\/B>, then click Cells<\/B>. (In Excel 2007 click Format<\/B> on the Home<\/B> ribbon and select Format Cells<\/B>.) On the Number<\/B> tab of the Format Cells<\/B> dialog box click on the Custom<\/B> 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.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Now look what happens when we echo back the value of cell A1:<\/P>
11\/22\/1986\n<\/PRE>\n
Is that cool or what?<\/P>\n
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\u2019s format to General):<\/P>
objExcel.Cells(1,1).NumberFormat = “”\n<\/PRE>\n
Will that really<\/I> turn our date back into a number? Well, let\u2019s echo back the value of cell A1 and see for ourselves:<\/P>
31738\n<\/PRE>\n
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\u2019s not a metaphor for the Scripting Guys and their Microsoft careers, well, we don\u2019t know what is.<\/P>\n
We hope that answers your question, TC. As for the Scripting Guys, it\u2019s not even lunch time yet, but we\u2019re already making preparations to head for home. Not because we\u2019re concerned about power outages; we just feel like going home. See you tomorrow. <\/P>\n
Oh: and could you bring some potato chips, preferably salt-and-vinegar? Thanks.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! We have a spreadsheet that includes numbers like this: 31738. However, this isn\u2019t supposed to be a number, it\u2019s 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 Hey, TC. Yes, as a matter of […]<\/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":[710,711,48,3,5],"class_list":["post-63753","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! We have a spreadsheet that includes numbers like this: 31738. However, this isn\u2019t supposed to be a number, it\u2019s 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 Hey, TC. Yes, as a matter of […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63753","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=63753"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63753\/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=63753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}