How Can I Create a Custom Date Format in Microsoft Excel?

ScriptingGuy1

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’s 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’s 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 bat on top of the one he got just a few months ago? Sure; why not?

Anyway, just let us know how much we owe you, CM; we didn’t 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:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1,1).Value = "01/01/2006"
objExcel.Cells(1,1).NumberFormat = "yyyymmdd"

As 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 object and then setting the Visible 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 method to add a new workbook. And once we have a workbook, we’re ready to get down to business.

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 of cell A1 (row 1, column 1) to 01/01/2006:

objExcel.Cells(1,1).Value = "01/01/2006"

That’s going to give us a spreadsheet that looks like this, with Excel applying its default date-time format to cell A1:

Of course, we don’t want 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 going to cost us? For once, at least, absolutely nothing:

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

Turns out that there’s nothing to this: we simply configure the cell’s NumberFormat property, assigning it a value consisting of four y’s (representing the four digits in the year); two m’s (representing the digits in the month); and two d’s (representing the two digits in the day). That’s all we have to do; like magic, the value in cell A1 will now be formatted like this:

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:

01.01.2006

Okey-doke:

objExcel.Cells(1,1).NumberFormat = "mm.dd.yyyy"

For more information on formatting cells, open up Excel, click Format, click Cells, and then, on the Number tab of the Format Cells dialog box click on the Custom category. Any of the formatting symbols you can see in the dialog box can be applied to the NumberFormat property using a script.

At any rate, consider that a down payment, CM and don’t worry: we’ll get the balance to you as quick as we can. How do you spell CM again?

ScriptingGuy1

Follow