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
- When the Property Falls Short
- Format Function versus NumberFormat Property
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
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:
- The first day of the year, and
- 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
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
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.
|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)|
||Print the named month of the year, abbreviated or in full (localized)|
|y||Print the numbered day of the year (
||Print the numbered day of the week(
|ww||Print the numbered week of the year(
|q||Print the quarter of the year(
|/||The date separator, localized unless escaped with a backslash|
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).
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,
Three Date Styles with the same underlying date
Since my system is set to US English, when I type
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",
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.
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?
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
w, respectively. This is another place where code memorization is important, as
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.
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.
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!