VBA DatePart

What is Excel VBA DatePart Function?

The Excel VBA DatePart function is a built-in function that allows you to extract specific components from a given date or time value. It is primarily used in Visual Basic for Applications (VBA) programming within Excel to manipulate and work with dates.

Let us look at an example. The code extracts the current hour from the myDateTime variable using the VBA DatePart function with the interval argument set to “h.” The “Now” function assigns the current date and time to myDateTime. The extracted hour is then displayed in a message box using the MsgBox function.

When you run this code, it will display a message box indicating the current hour. For example, if the current time is 09:00 PM, the message box will display “The current hour is: 21”.

Syntax

The syntax for the DatePart function is as follows:

DatePart(interval, date[, firstdayofweek[, firstweekofyear]])

The function takes the following arguments:

interval: A string expression that specifies the interval of time you want to extract. It can be one of the following values:

Interval	Description
“yyyy”	Year
“q”	Quarter
“m”	Month
“y”	Day of year
“d”	Day
“w”	Weekday
“ww”	Week of year
“h”	Hour
“n”	Minute
“s”	Second

date: A valid date or time expression from which you want to extract the specified interval.

firstdayofweek: (Optional) An optional value that specifies the first day of the week. It can be one of the following values:

vbUseSystemDayOfWeek: Use the system setting for the first day of the week (default).

Value	Description
vbSunday	Sunday (default)
vbMonday	Monday
vbTuesday	Tuesday
vbWednesday	Wednesday
vbThursday	Thursday
vbFriday	Friday
vbSaturday	Saturday

firstweekofyear: (Optional) An optional value specifying the week’s first day. It can be one of the following values:

vbUseSystem: Use the system setting for the first week of the year (default).

vbFirstJan1: The week containing January 1.

vbFirstFourDays: The first week that has at least four days in the new year.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use DatePart Function in VBA (with Steps)

To use the DatePart function in VBA, follow these steps:

Step 1: Open Excel and press Alt + F11 to open the VBA Editor.

Step 2: Insert a new module by clicking Insert from the top menu and selecting Module.

Step 3: Write the VBA code in the newly created module that will use the DatePart function. Declare the necessary variables.

For example:

Dim result As Integer
Dim myDate As Date

Step 4: Assign a date or time value to the myDate variable.

myDate = #7/11/2023#

Step 5: Use the DatePart function to extract the desired interval from the date. For example, to extract the year:

result = DatePart(“yyyy”, myDate)

Step 6: Display or use the result as needed.

MsgBox result

Step 7: Run the VBA code by pressing F5 or clicking the “Run” button in the toolbar.

Examples

Let us look at a few examples of how to use the DatePart function in Excel.

Example 1: Extracting the Year from a Date using VBA DatePart Year function.

Step 1: Start the subroutine “YearFromDate” by typing Sub YearFromDate().

Step 2: Declare an integer variable named “result” to store the extracted year value.

Step 3: Declare a date variable named “myDate” to store the specific date from which the year will be extracted.

Step 4: Assign the date value #7/11/2023# to the “myDate” variable.

Note: In VBA, the # symbol is used as a delimiter to represent a date literal. When you enclose a date within # symbols, it signifies that you provide a date value directly rather than a string representation of a date.

Step 5: The VBA DatePart year function extracts the year from the “myDate” variable. Store the extracted year value in the “result” variable.

Step 6: Display the value of “result” in a message box using the code “MsgBox result.”

Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select YearFromDate, and run.

Step 8: Once you execute the code, you will find the resulting year, 2023, displayed using a message box.

Here is the complete code:

Sub YearFromDate()
Dim result As Integer
Dim myDate As Date
myDate = #7/11/2023#
result = DatePart(“yyyy”, myDate)
MsgBox result
End Sub

Example 2: Extracting the Month from a Date (Two-Digit Format)

Step 1: Start the subroutine “MonthFromDate” by typing Sub MonthFromDate().

Step 2: Declare an integer variable named “result” to store the extracted month value.

Step 3: Declare a date variable named “myDate” to store the specific date from which the month will be extracted.

Step 4: Assign the date value #7/11/2023# to the “myDate” variable.

Step 5: The DatePart function extracts the month from the “myDate” variable. Store the extracted month value in the “result” variable.

Step 6: Display the value of “result” in a message box formatted as a two-digit number.

Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select MonthFromDate and run.

Step 8: Once you run the code, you will find the resulting month, 7, is then formatted using VBA DatePart two-digit month “Format” function, and the formatted result, “07”, is displayed using a message box.

Here is the full code:

Sub MonthFromDate()
Dim result As Integer
Dim myDate As Date
myDate = #7/11/2023#
result = DatePart(“m”, myDate)
MsgBox Format(result, “00”)
End Sub

Example 3: Extracting the Day of the Year

Step 1: Start the subroutine “DayOfYear” by typing Sub DayOfYear().

Step 2: Declare an integer variable named “result” to store the extracted day of the year value.

Step 3: Declare a date variable named “myDate” to store the specific date from which the day of the year will be extracted.

Step 4: Assign the date value #7/11/2023# to the “myDate” variable. Type myDate = #7/11/2023#. Adjust the date as needed.

Step 5: Use the DatePart function to extract the day of the year from the “myDate” variable. Store the extracted value in the “result” variable.

Step 6: Display the value of “result” in a message box. Type MsgBox result.

Step 7: Save the module and close the VBA. Now press Alt + F8 to open the Macro menu, select DayOfYear and run.

Step 8: Once you execute the code, you will find the resulting day of the year, 192, which is formatted using the VBA DatePart day function and displayed using a message box.

Here is the full code:

Sub DayOfYear()
Dim result As Integer
Dim myDate As Date
myDate = #7/11/2023#
result = DatePart(“y”, myDate)
MsgBox result
End Sub

Usage of DatePart Function

1. The VBA DatePart function is commonly used in VBA programming within Excel when working with dates. Here are some common use cases:
2. Data Analysis: The VBA DatePart function can analyze data based on specific time intervals. For example, you can extract the month from a date and group data by month for further analysis or reporting purposes.
3. Conditional Formatting: In conditional formatting rules, you can use the VBA DatePart function to highlight cells based on specific dates or time intervals. For instance, you can highlight all weekends or specific days of the week within various dates.
4. Date Calculations: The VBA DatePart function can be combined with other mathematical operations to perform calculations involving date or time intervals. It enables you to extract specific components from dates and perform calculations based on those components.
5. Date Formatting: You can use the VBA DatePart function to extract date components and format them according to your requirements. It can be helpful when generating custom-formatted date strings or populating reports with specific date information.

Important Things To Note

1. The VBA DatePart function works with properly formatted date or time values.
2. The interval argument should be enclosed in double quotation marks (“”) and match the desired interval you want to extract. Any invalid or misspelled interval will result in an error.
3. If you omit the optional arguments firstdayofweek and firstweekofyear, the function will use the default settings defined in the system.
4. The VBA DatePart function returns an integer value representing the extracted interval. Ensure the variable receiving the result is of the appropriate VBA data type (Integer) to avoid type mismatch errors.
5. The VBA DatePart function extracts date components, while the TimePart function extracts time components.
6. The VBA DatePart function uses the Gregorian calendar system for calculations. Ensure that your dates fall within the supported range of the Gregorian calendar (January 1, 100 to December 31, 9999).

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA DatePart formulas and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is VBA DatePart. We learn the steps to use DatePart function in excel with examples and downloadable template. You can learn more from the following articles –

• VBA ComboBox
• VBA DateSerial
• VBA CDATE Function