VBA Date Format

The Date Format function takes a date expression, and it returns a string containing an expression formatted according to the instructions.

In Excel VBA, the Format function is a built-in function, and it is categorized into two functions:

  • Date Function.
  • Time function.

There are two ways for formatting the dates in Excel VBA, such as:

  1. The NumberFormat property of cells: To format dates stored in cells.
  2. The VBA Format function: To format dates in VBA, for example, Variables.

Syntax

The Excel VBA Date Format follows the following syntax:

Explanation

  • Expression (Required): It stands for the value to be formatted.
  • Format (Optional): It is a user-defined format to be applied to the expression. We can define our format or use any predefined named formats, such as:
FormatExplanation
General DateIt displays a date, which format is based on the system settings.
Short DateIt displays a date, which format is based on the system’s short date setting.
Medium DateIt displays a date, which format is based on the system’s medium date setting.
Long DateIt displays a date, which format is based on the system’s long date setting.
Short TimeIt displays the time that depends on the system’s short time setting.
Medium TimeIt displays the time that depends on the system’s medium time setting.
Long TimeIt displays the time that depends on the system’s long time setting.
  • FirstDayOfWeek (Optional): It is a value that declares the first day of the week. If the first day of the week is not declared, then the format function supposes that Sunday is the first day of the week. It can be any of the given below values, such as:
ConstantValueExplanation
vbUseSystem0It uses the NLS API setting.
vbSunday1Sunday (default)
vbMonday2Monday
vbTuesday3Tuesday
vbWednesday4Wednesday
vbThursday5Thursday
vbFriday6Friday
vbSaturday7Saturday
  • FirstWeekOfYear (Optional): It is a value that declares the first week of the year. If the first week of the year is not declared, then the format function supposes that the first week is starting from 1st It can be any of the given below values, such as:
ConstantValueExplanation
vbUseSystem0It uses the NLS API setting.
vbFirstJan11It is a week that contains 1st January.
vbFirstFourDays2The first week that has at least 4 days in a year.
vbFirstFullWeek3The first full week of the year.

Example

Step 1: First, click on the Developer tab and select the Visual Basic option.

Step 2: Click on the Insert button and create a new Module.

Step 3: Double click on the newly added Module, and it will open a code window.

Step 4: Write the VBA Date Format function code, such as:

VBA Date Format

The VBA Date Format function is categorized into a string type of variables.

Dim is used to declare a variable name and its type.

After the Format function, the Range function is used to get output in a specific cell, the final code format to be used for a short date.

Step 5: Now click on the Run button and execute the above code, it will give the following output, such as:

VBA Date Format

Example 2: We can also create the user-defined Date Formats, such as:

Step 1: Write the following code on the code window.

VBA Date Format

In the above code, the Format function is applied to different user-defined date formats according to today’s date.

Step 2: Now click on the Run button and execute the above code, it will give the following code, such as:

VBA Date Format