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

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:

Format Function Intellisense
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.

Symbols Effects
d, dd Print the numbered day of the month, with or without a leading zero
m, mm Print the numbered month of the year, with or without a leading zero
yy, yyyy Print the year, either as a two digit or four digit number
ddd, dddd Print the named day of the week, abbreviated or in full (localized)
mmm, mmmm
Print the named month of the year, abbreviated or in full (localized)
y Print the numbered day of the year (Format function only)
w
Print the numbered day of the week(Format function only)
ww Print the numbered week of the year(Format function only)
q Print the quarter of the year(Format function only)
/ The date separator, localized unless escaped with a backslash

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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?

Date Formats with Four Digit Years and Three Letter Months
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.

Formatted 02/03/04 in different styles
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"

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

Arguments of Format Function with FirstDayOfYear Intellisense Activated
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:

Output of Function with Feb 2nd, 1985 as input
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.


Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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!

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!

Share this article on Google+, Twitter and Facebook, then leave a comment below and let’s have a discussion.

This article was written by Cory Sarver on behalf of The VBA Tutorials Blog.

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.