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:
- The NumberFormat property of cells: To format dates stored in cells.
- 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:
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:
Example 2: We can also create the user-defined Date Formats, such as:
Step 1: Write the following code on the code window.
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:
