VBA Format Date

This tutorial will demonstrate how to format dates using VBA.

Format Dates in VBA

There are two methods to format dates using VBA.

  1. The NumberFormat property of Cells / Ranges – To format dates stored in cells
  2. The VBA Format function – To format dates in VBA (ex. variables)

NumberFormat – Dates

The default number format of a cell in Excel is General. You can display values as numbers, text, dates, percentages or currencies by changing the number format. The NumberFormat property can be used in VBA to set the number format of dates in a cell or range.

Note: In order to see the different standard date formats available in Excel go to Home>Number and you’ll see options such as Short Date, Long Date and Time.

vba number format
Number Format Options

AutoMacro - VBA Code Generator

Short Date

The Short date number formatting option displays the date in a short, numerical format.

The following code will set the .NumberFormat property of cell A1 to Short date:

Long Date

Long date number formatting displays the date in a longer, written format. The following code will set the .NumberFormat property of cell A1 to Long date:

Custom dates

In order to see the custom number format codes that you can use in VBA to format dates, go to Home>Number and click the Dialog Box Launcher.  Select the Number tab and choose Custom.

vba number format date

You can either select the custom built-in formats for your date or create your own user-defined date formats. The following code will set the .NumberFormat property of cell A1 to a built-in custom date format:

The result is:

Custom Date Format in VBA

The following code will set the .NumberFormat property of cell A1, to a user-defined custom date format:

The result is:

User Defined Custom Date Formatting

By reviewing Excel’s pre-built examples you can learn how NumberFormats should be entered. For more information, read Microsoft’s documentation on number formats for dates.

VBA Format Function

As mentioned above, the NumberFormat method is appropriate for setting the Numberformat of dates stored in Excel cells.  In VBA, you can use the Format Function to convert dates to strings with certain date formatting.

You would use the following syntax to format dates:

Format(String_Representation, NumberFormatCode) where:

String_Representation – the text string representing the date.

NumberFormatCode – the number format code specifying the way the date should be displayed.

The following code shows how to format a text string representation of a date as long date format:

Formatting Dates as Long Dates

The result is:

Format Function in VBA

 

Notice that the Format Function uses the same date formatting syntax as the NumberFormat above.

The following code shows how to format a text string representation of a date as medium date format:

The result is:

Medium Date Formatting in VBA

The following code shows how to format a text string representation of a date as a user-defined format:

Custom Formatting

VBA Custom Format Builder

Our VBA Add-in: AutoMacro contains a Custom Format Builder for the VBA Editor. This allows you to set custom formats and immediately preview the output for your desired value:

vba custom number format

The VBA Add-in contains numerous other “Code Generators”, an extensive code library, and an assortment of other coding tools.  It’s the ultimate add-in for anyone serious about VBA programming!

VBA Format Date in Access

The VBA Format Date function work exactly the same in Access VBA as in Excel VBA.

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)