VBA Format Date with these Format Codes

In the our VBA NumberFormat tutorial we discussed number formatting for easier readability. In this tutorial, we will look at VBA date formatting in a similar way. This tutorial mostly focuses on writing custom date formats, just like we wrote custom number formats in our NumberFormat article.

• Assigning Formats
  
• VBA Date Format Codes
  
  • Table of Date Format Codes
    
  • Two Digit Dates
    
  • Localized Abbreviations
    
  • Naming the Days of the Week
    
  • Date Separator and Literals
    
• When the Property Falls Short
  
• Format Function versus NumberFormat Property
  
• Conclusion
  

Assigning Formats

The VBA Date format and the Number format concepts are closely related. Because of the connection, you can assign a date format using the NumberFormat property of a Range object, just like you would to format a number in a cell. We could have a macro that looks something like this:

Sub format_via_property()
Dim cell_to_format As Range
Dim vector_to_format As Range
Dim matrix_to_format As Range

Set cell_to_format = Range("A1")
Set vector_to_format = Range("B1:B100")
Set matrix_to_format = Range("C1:D50")

range_to_format.NumberFormat = "YOUR FORMAT CODE HERE"

End Sub

The YOUR FORMAT CODE HERE string is replaced with a custom VBA date format string, like mm/dd/yyyy.

You can also use the Format function, which takes a string (your date) and formats it according to the code. For numbers, it offers little extra advantage, but for dates, there are two useful optional arguments:

1. The first day of the year, and
2. The first day of the week.

Here’s how the format function looks:

The Format function with possible parameters

It’s important to note that Format is a function, which makes it ideal for formatting dates (and numbers) on the fly for immediate output format conversion. This is useful if the user is interacting directly with the VBA and does nothing with the Excel spreadsheet itself (or any other MS Office product in which your VBA code is embedded).

VBA Date Format Codes

Now that we know how to assign the format to the spreadsheet or format it on the fly without directly accessing Range properties, we can look at how to actually write the codes. Unless specified, these codes work for both the NumberFormat property and the Format function.

The number codes were rather cryptic for first timers, using various symbols and allowing for a wide range of patterns. Date codes are far less mysterious for English users, but some codes with very similar implementations have drastically different outputs (like y and yy). We’ll go over these nuances in detail.

Table of Date Format Codes

Here’s a little cheat sheet of all the VBA date format codes. We’ll go over them in more detail later in this tutorial.

Simple Two-Digit Codes

The simplest date is something like 10/10/10. It doesn’t matter which date format your culture uses (mm/dd/yy, dd/mm/yy, or yy/mm/dd), because all numbers are the same in this example. This cloaked ambiguity makes for an interesting formatted date, so let’s use it.

As promised, date codes are much less cryptic than number codes. mm means “give me two numbers for the month (01 - 12)”. dd means “give me two numbers for the day of the month (01-31)”. And yy means “give me the last two digits of the year (00-99)”. In fact, we could even use a code like m d yy if we wanted to. These will return single digits for months and days less than 10, but two digits for days and months with numbers equal to 10 or greater.

Similar to the 0 from NumberFormats, the double month and double day will add a zero to the front if the number is missing (01, 02, 03), but the single month and single day codes will simply print single digits for numbers less than 10 (1, 2, 3).

Localized Abbreviations

Let’s say we want to eliminate all ambiguity and use the three letter abbreviation for the month and a full four digit number for the year. Can you guess the format?

Three Styles of Dates with the Same Base Formatting

The US format is the same as mmm dd yyyy. Now there is no ambiguity whatsoever. It is impossible to mistake a number like 10/10/10 or 02/03/04, because the month, day, and year are always in a unique format. Another important feature is these codes are localized, so if your system is set to German, Okt will appear instead of Oct. Localized is just another way of saying it depends on your geographic region.

Three Date Styles with the same underlying date

Since my system is set to US English, when I type 02/03/04, my system always sees it as mm/dd/yy. Thus the underlying value in Columns B and C is February 3rd, 2004, and only the displayed format is changed. The input is not formatted by the code; only the output (display) is formatted. This is critical to remember! It means the programmer must know how the user will input dates, because Excel will use the system’s settings to determine the ordering of the input.

To get the full name of the month, use mmmm (four ms). Again, this is localized, so you will end up with August or Août, depending on whether your system is set to English or French.

Including the Day of the Week

Sometimes it is useful to include the day of the week, like Friday or Saturday. This is where date formats require a little memorization.

The short code ddd is not some strange triple digit day but the three letter code corresponding to the day of the week (Fri, Sat).

dddd, on the other hand, spits out the full name (Saturday or Samstag; this is localized based on the system).

If we formatted a date using "ddd dd mmmm yyyy", 02/03/04, as entered on a US English system, will appear as Tue 03 February 2004.

We could even write

cell_to_formatEU.NumberFormat = "ddd \t\h\e dd \o\f mmmm yyyy"

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Recall from our VBA NumberFormat post that the backslash \ allows you to write characters in the string without using double quotes (""text""). Our output? Tue the 03 of February 2004.

The Date Separator and Literals

Another localized input is using the forward slash / as the date separator. If the local system uses a different separator in general, it will be used in place of /. On the other hand, if you want to force another symbol, such as a period (which I find to be cleaner), you can simply add it to the code, like so:

cell_to_format.NumberFormat = "dd.mm.yyyy"

If you want to force the forward slash as the separator, even on non-US systems, you have to use the escape character, the backslash, like this:

cell_to_format.NumberFormat = "dd\/mm\/yyyy"

This will ignore the localization effects of the forward slash and always force a forward slash.

When the NumberFormat Property Falls Short

Sometimes it is useful to use a number to represent the day of the year, week of the year, or day of the week. These are displayed with y, ww, and w, respectively. This is another place where code memorization is important, as y and yy will output very different things. You can also show the quarter with q. One catch is that the day of the week and the week of the year are dependent on when the week or year begins.

Due to this variability, the NumberFormat property cannot be used with y, w, ww, or q. If you use y in NumberFormat, it will be interpreted as yy. If you use the others, they will simply appear as text.

So, let’s look at how to implement these formats using the Format function.

Sub format_with_week_day_quarter()
Dim source_cell As Range
Dim mon_dest_cell As Range
Dim sun_dest_cell As Range
Set source_cell = Range("B1")
Set mon_dest_cell = Range("B2")
Set sun_dest_cell = Range("B3")

mon_dest_cell = Format(source_cell, "\Qq \Www \Dw ddd mm yyyy", vbMonday, vbFirstJan1)
sun_dest_cell = Format(source_cell, "\Qq \Www \Dw ddd mm yyyy", vbSunday, vbFirstJan1)

End Sub

The Format function with Intellisense showing the options for "First Week of Year

And remember, Format is a function, so the output is what we need to assign to the cells. We will end up with this output:

The Quarter, Week Number, and Day of the Week plus the date itself

From this, we can think of some creative applications of the Format function. One might be to use the output from CInt(Format(DATE, "q")) to determine which quarterly report to attach to your email.

Format Function versus NumberFormat Property

As we already saw, sometimes we are forced to use the Format function, rather than the NumberFormat property, to get our desired result. Some might think “why even use the NumberFormat property at all, since it has some major shortcomings?”

My answer to that question is based on what a Function does and what a Property does. The function will return an output, but only after it’s received an input. For VBA, that means you must run the function every time you change the cell to get the formatted output. Using the Worksheet_Change Event, this is certainly doable, but it will result in unnecessary computation.

Conversely, Excel is already optimized to format the input without the need to call some user’s function. Sure, Excel technically is processing the input, but it’s optimized deep down in the source code of MS Office (and maybe even the OS, but I’m not a Microsoft developer).

Of course, you might actually need to process input. If you’re going to participate in some functional programming by feeding the output quarter directly into the input of another function, then the Format function is the right tool.

The code myColor = RGB(CInt(Format(DATE, "q"))*60, 0, 0) is an example of what I mean. Depending on which quarter of the year you’re currently in, the color (the redness, in particular) will change.

Conclusion

This concludes our tutorial on VBA date formats. There’s nothing magical about them; they behave much the same as the date formats in the Excel number formatting menu. The differences is, with VBA you can format the dates using both the NumberFormat property and the Format Function.

Hopefully the creative example of changing color based on the quarter will lead you to some other inspirational ideas for using the format function. At the very least, you will now be able to develop easy-to-read and beautified spreadsheets to impress your coworkers!

For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below. Then, share what you learned with others on Twitter and Facebook.