VBA Date Format. The Date Format function accepts a date expression as input and returns a string comprising the expression in the specified format. The Format function is a built-in one in Excel VBA that may be broken down into two subsets:
- Date Function
- Time Function
Dates can be formatted in Excel VBA in two distinct methods
- NumberFormat property of cells
- The VBA Format function. For example, Variables
See More: Excel VBA Find
Syntax
Here is the syntax for Excel VBA’s date format:
Format (expression, [format, [firstdayofweek, [firstweekofyear]])
- Expression (Necessary): It represents the formatted value.
- Format (Optional): It is a format that the user sets up for the expression. There is no restriction on whether or not we utilise our custom-defined format or predefined named formats.
| Format | Explanation |
| General Date | It shows a date in a format that depends on how the system is set up. |
| Short Date | It shows a date in the system’s format set for short dates. |
| Medium Date | It shows a date in a format that depends on how the system’s medium date is set. |
| Long Date | It shows a date in a format that depends on how the system sets up for long dates. |
| Short Time | It shows the time based on how the system sets up for a short time. |
| Medium Time | It shows the time based on the system’s setting for a medium time. |
| Long Time | It shows the time based on the system’s setting for a long time. |
FirstDayOfWeek (Optional): The value states the first day of the week. If the format function is not given a declaration for the first day of the week, it will assume Sunday as the first day. It can have any of the following values:
| Constant | Value | Explanation |
| vbUseSystem | 0 | 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 tells the first week of the year. If the first week of the year is not specified, the format function assumes that the first week starts on the 1st.
| Constant | Value | Explanation |
| vbUseSystem | 0 | It uses the NLS API setting. |
| vbFirstJan1 | 1 | The week is the first of January. |
| vbFirstFourDays | 2 | The 1st week has at least four days in a year. |
| vbFirstFullWeek | 3 | The 1st whole week of the year. |
Example
Step 1: Move to the Developer tab and choose Visual Basic from the list.
Step 2: Create a new Module by clicking the Insert button.
Step 3: Double-click the Module you just added to create a code window.
Step 4: Write the code for the VBA Date Format function as shown below:
Sub DateFormat_Example() Dim strDate As String strDate = "30-November-2022" MsgBox Format(strDate, "DD/MM/YY") End Sub
Step 5: Now, click the Run button and run the code above. It will give you results like:
