![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a><\/p>\nHey, CM. Who do we make the check out to again? And for how much?<\/p>\n
Oh, sorry. After attending a parent meeting for the Scripting Son\u2019s new baseball team the Scripting Guy who writes this column naturaly assumes that anyone who asks him a question is asking for money. You need $1,800 to cover the Scripting Son\u2019s expenses for winter workouts and for the season? OK. The Scripting Family is expected to buy 6 tickets to the annual fund-raising auction? Uh, all right. The coach has decided to play in a wood bat league, meaning that the Scripting Son will need another<\/i> bat on top of the one he got just a few months ago? Sure; why not?<\/p>\n
Anyway, just let us know how much we owe you, CM; we didn\u2019t bring the checkbook to work today. In the meantime, here’s a script that can apply a custom date format to a cell in a Microsoft Excel spreadsheet:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nobjExcel.Workbooks.Add\nobjExcel.Cells(1,1).Value = \"01\/01\/2006\"\nobjExcel.Cells(1,1).NumberFormat = \"yyyymmdd\"\n<\/pre>\nAs you can see, applying a custom date format is as easy as spending money. We start out 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 Microsoft Excel that we can see on screen. After we have Excel up and running we then use the Add<\/b> method to add a new workbook. And once we have a workbook, we’re ready to get down to business.<\/p>\n
To begin with, we need to put the value 01\/01\/2006 into the spreadsheet. This line of code does just that, setting the Value<\/b> of cell A1 (row 1, column 1) to 01\/01\/2006:<\/p>\nobjExcel.Cells(1,1).Value = \"01\/01\/2006\"\n<\/pre>\nThat\u2019s going to give us a spreadsheet that looks like this, with Excel applying its default date-time format to cell A1:<\/p>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/exceldate1.jpg\")
<\/p>\n
Of course, we don\u2019t want<\/i> the default date-time format; we want to use a custom format, one that displays the year using four digits, the month using two digits, and the day using two more digits. How much is that<\/i> going to cost us? For once, at least, absolutely nothing:<\/p>\nobjExcel.Cells(1,1).NumberFormat = \"yyyymmdd\"\n<\/pre>\nTurns out that there’s nothing to this: we simply configure the cell\u2019s NumberFormat<\/b> property, assigning it a value consisting of four y\u2019s<\/i> (representing the four digits in the year); two m\u2019s<\/i> (representing the digits in the month); and two d\u2019s<\/i> (representing the two digits in the day). That\u2019s all we have to do; like magic, the value in cell A1 will now be formatted like this:<\/p>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/exceldate2.jpg\")
<\/p>\n
We should probably point out that you can create pretty much any custom date-time format you want. For example, suppose you’d like to display the date like this:<\/p>\n
01.01.2006\n<\/pre>\nOkey-doke:<\/p>\n
objExcel.Cells(1,1).NumberFormat = \"mm.dd.yyyy\"\n<\/pre>\nFor more information on formatting cells, open up Excel, click Format<\/b>, click Cells<\/b>, and then, on the Number<\/b> tab of the Format Cells<\/b> dialog box click on the Custom<\/b> category. Any of the formatting symbols you can see in the dialog box can be applied to the NumberFormat property using a script.<\/p>\n
At any rate, consider that a down payment, CM and don\u2019t worry: we\u2019ll get the balance to you as quick as we can. How do you spell CM<\/i> again?<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! In Microsoft Excel, how can I display a date like 01\/20\/2006 using this format: 20060120? — CM Hey, CM. Who do we make the check out to again? And for how much? Oh, sorry. After attending a parent meeting for the Scripting Son\u2019s new baseball team the Scripting Guy who writes this […]<\/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,5],"class_list":["post-66083","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-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! In Microsoft Excel, how can I display a date like 01\/20\/2006 using this format: 20060120? — CM Hey, CM. Who do we make the check out to again? And for how much? Oh, sorry. After attending a parent meeting for the Scripting Son\u2019s new baseball team the Scripting Guy who writes this […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66083","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=66083"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66083\/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=66083"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66083"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66083"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")