VBA Date Format

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.

VBA Module

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:

DateFormat

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top