spot_img

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:
Format Explanation
General Date It displays a date, which format is based on the system settings.
Short Date It displays a date, which format is based on the system’s short date setting.
Medium Date It displays a date, which format is based on the system’s medium date setting.
Long Date It displays a date, which format is based on the system’s long date setting.
Short Time It displays the time that depends on the system’s short time setting.
Medium Time It displays the time that depends on the system’s medium time setting.
Long Time It 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:
Constant Value Explanation
vbUseSystem 0 It uses the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
  • 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:
Constant Value Explanation
vbUseSystem 0 It uses the NLS API setting.
vbFirstJan1 1 It is a week that contains 1st January.
vbFirstFourDays 2 The first week that has at least 4 days in a year.
vbFirstFullWeek 3 The 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


Next TopicVBA Workbooks Open

spot_img
Previous articleVBA Data Types
Next articleCircular reference in Excel