VBA DatePart Function

The DatePart function in VBA (Visual Basic for Applications) is used to extract a specified part of a given date. It is a built-in function that allows users to manipulate date and time values in VBA. The DatePart function takes three arguments – interval, date, and [firstdayofweek] – and returns a specified part of the date or time, such as the day, month, year, or hour.

VBA DatePart Function – Purpose, Syntax and Arguments

Purpose:

The DatePart function is useful for performing various calculations or manipulations on date values. It allows users to extract or display specific parts of a date, making it easier to work with date and time values in VBA. It can be used in various scenarios, such as sorting data by month or year, calculating the number of days between two dates, or displaying the day of the week for a given date.

Syntax:

DatePart(interval, date, [firstdayofweek])

Arguments:

• interval: This is the first required argument, which specifies the part of the date you want to extract. It is specified as a string and can take the following values: “yyyy” (year), “q” (quarter), “m” (month), “d” (day), “w” (weekday), “y” (day of the year), “ww” (week of the year), “h” (hour), “n” (minute), or “s” (second).
• date: This is the second required argument, which represents the date from which you want to extract the specified interval. It can be a date, string, or expression that can be interpreted as a date.
• firstdayofweek: This is an optional argument that specifies the first day of the week. It is used when the “w” interval is specified and can take the following values: vbUseSystem (default), vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, or vbSaturday.

Example:

Let’s say we have a list of orders with their corresponding dates, and we want to extract the month from each date and display it in a new column. We can use the DatePart function with the “m” interval to achieve this.

Sub ExtractMonth()
Dim lastRow As Long
Dim i As Long
'find last row of data
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'loop through each row
For i = 2 To lastRow
'extract month and paste in column B
Cells(i, 2) = DatePart("m", Cells(i, 1))
Next i
End Sub

In this example, we are using a For loop to iterate through each row, using the DatePart function to extract the month from the date in column A and paste it in column B. The result would look like this:

Remarks:

• The DatePart function is a useful tool for manipulating date and time values in VBA.
• If the interval argument is not specified correctly or if the date argument is not a valid date, the function will return an error.
• If the firstdayofweek argument is not specified, it will default to the system’s settings.
• If the firstdayofweek argument is specified and the interval is not “w,” it will be ignored.

Important Notes:

• The DatePart function is only available in VBA and cannot be used in Excel formulas.
• The interval argument is case-sensitive.
• The firstdayofweek argument can vary depending on the user’s regional settings. It is recommended to specify it explicitly to avoid any discrepancies in the results.

Understanding VBA DatePart Function with Examples

Visual Basic for Applications (VBA) is a programming language that is used with Microsoft Office applications to create custom macros and automate tasks. One of the most commonly used functions in VBA is the DatePart function, which is used for manipulating and extracting specific parts of a date.

The DatePart function takes three arguments: the interval, the date, and the first day of the week. The interval can be any of the following: “yyyy” for the year, “q” for the quarter, “m” for the month, “d” for the day, “ww” for the week, “h” for the hour, “n” for the minute, and “s” for the second.

Extracting the Year from a Date

In this example, we will be using the DatePart function to extract the year from a date. First, we declare a variable ‘myDate’ and assign it a specific date value using the ‘DateValue’ function. Then we use the DatePart function to extract the year from this date and assign it to the variable ‘myYear’. Finally, we use the ‘MsgBox’ function to display the result. The following code represents the above description:

Dim myDate As Date
myDate = DateValue("12/31/2019")
Dim myYear As Integer
myYear = DatePart("yyyy", myDate)
MsgBox "The year is " & myYear

The output of this code will be: The year is 2019

In this example, we used the interval “yyyy” to extract the year from the date. If we had used “yy” instead, the result would have been “19”. The interval used depends on the desired output.

Finding the Day of the Week

In this example, we will use the DatePart function to find the day of the week for a given date. We declare a variable ‘myDate’ and assign it a specific date value. Then we use the DatePart function with the interval “w” to extract the weekday number. Finally, we use a ‘Select Case’ statement to determine the weekday name based on the weekday number. The following code represents the above description:

Dim myDate As Date
myDate = DateValue("11/13/2019")
Dim weekDay As Integer
weekDay = DatePart("w", myDate)
Select Case weekDay
Case 1
MsgBox "The day of the week is Sunday"
Case 2
MsgBox "The day of the week is Monday"
Case 3
MsgBox "The day of the week is Tuesday"
Case 4
MsgBox "The day of the week is Wednesday"
Case 5
MsgBox "The day of the week is Thursday"
Case 6
MsgBox "The day of the week is Friday"
Case 7
MsgBox "The day of the week is Saturday"
End Select

The output of this code will be: The day of the week is Wednesday

The weekday number returned by the DatePart function follows the VBA standard, where Sunday is considered the first day of the week and has a value of 1.

Calculating the Difference Between Two Dates

In this example, we will use the DatePart function to calculate the difference between two dates.
We declare two variables ‘startDate’ and ‘endDate’ and assign them specific date values. Then we use the DatePart function with the interval “d” to extract the number of days between the two dates. Finally, we use the ‘MsgBox’ function to display the result.
The following code represents the above description:

Dim startDate As Date
Dim endDate As Date
startDate = DateValue("02/03/2020")
endDate = DateValue("02/10/2020")
Dim daysDifference As Integer
daysDifference = DatePart("d", endDate, startDate)
MsgBox "The number of days between the two dates is " & daysDifference

The output of this code will be: The number of days between the two dates is 7

In this example, we used the interval “d” which returns the difference between two dates in days. It is important to note that the positioning of the two dates in the DatePart function matters. The first date should be the later date and the second date should be the earlier date, otherwise, the result will be a negative number.

Finding the Quarter of a Given Date

In this example, we will use the DatePart function to find the quarter of a given date. We declare a variable ‘myDate’ and assign it a specific date value. Then we use the DatePart function with the interval “q” to extract the quarter number. Finally, we use the ‘MsgBox’ function to display the result. The following code represents the above description:

Dim myDate As Date
myDate = DateValue("04/10/2020")
Dim quarter As Integer
quarter = DatePart("q", myDate)
MsgBox "The quarter is " & quarter

The output of this code will be: The quarter is 2

In this example, we used the interval “q” which returns the quarter number of a given date. This can be particularly useful when working with financial data and analyzing quarterly reports.

Getting the Hour, Minute, and Second of a Time Value

In this example, we will use the DatePart function to extract the hour, minute, and second from a given time value. We declare a variable ‘myTime’ and assign it a specific time value. Then we use the DatePart function with the intervals “h”, “n”, and “s” to extract the hour, minute, and second respectively. Finally, we use the ‘MsgBox’ function to display the result. The following code represents the above description:

Dim myTime As Date
myTime = TimeValue("10:30:45 AM")
Dim hours As Integer
Dim minutes As Integer
Dim seconds As Integer
hours = DatePart("h", myTime)
minutes = DatePart("n", myTime)
seconds = DatePart("s", myTime)
MsgBox "The time is " & hours & ":" & minutes & ":" & seconds

The output of this code will be: The time is 10:30:45

In this example, we used the intervals “h”, “n”, and “s” to extract the hour, minute, and second respectively. This can be useful when working with time-sensitive data or creating reports with time stamps.

The DatePart function in VBA is a powerful tool for manipulating and extracting specific parts of a date or time value. It allows for a wide range of intervals to be used, enabling programmers to perform various operations on dates and times. By understanding the different intervals and how they are used, you can take full advantage of this function in your VBA projects. We hope the examples provided in this blog post have helped you better understand the DatePart function and how it can be implemented in your code.