Rifat Hassan
One of the most important and widely used functions that we encounter while working with VBA is the DatePart function of VBA. Today in this article, I’ll show you how you can use the DatePart function of VBA with proper examples and illustrations.
It’ll return 20, the day of the date “20/11/2019”.
Excel VBA DatePart Function Overview
⧭ Function Objective:
The DatePart function returns a specific portion of a given date stated by a specific interval. There are a total of 10 intervals available for use within the DatePart function.
Each interval returns a distinct portion of the date.
⧭ Syntax:
⧭ Arguments:
| Argument | Required / Optional | Explanation |
|---|---|---|
| Interval | Required | Denotes the specific portion of the date that’ll be returned. |
| Date | Required | The date from which a specific part will be returned. |
| FirstDayofWeek | Optional | Specifies the first day of the week. Optional. The default is Sunday. |
| FirstWeekofYear | Optional | Specifies the first week of the year. Default is the week that contains January 01. |
There are a total of 10 intervals available for use within the DatePart function.
| Interval | Return Value |
|---|---|
| d | Day of the Date |
| m | Month of the Date |
| yyyy | Year of the Date |
| q | Quarter of the Date |
| y | Day of the Year of the Date |
| w | Weekday of the Date |
| ww | Week Number of the Year of the Date |
| h | Hour of the Time |
| n | Minute of the Time |
| s | Second of the Time |
And a total of 8 constant values can be used as the FirstDayofWeek.
| FirstDayofWeek | Explanation |
|---|---|
| vbUseSystem | Uses the NLS API setting to dictate the first day of the week. |
| vbSunday | Sets Sunday as the first day of the week. |
| vbMonday | Sets Monday as the first day of the week. |
| vbTuesday | Sets Tuesday as the first day of the week. |
| vbWednesday | Sets Wednesday as the first day of the week. |
| vbThursday | Sets Thursday as the first day of the week. |
| vbFriday | Sets Friday as the first day of the week. |
| vbSaturday | Sets Saturday as the first day of the week. |
Also, a total of 4 constant values can be used as the FirstWeekofYear.
| FirstWeekofYear | Explanation |
|---|---|
| vbUseSystem | Uses the NLS API setting to dictate the first week of the year. |
| vbFirstJan1 | Sets the week with January 1 as the first week of the year. |
| vbFirstFourDays | Sets the week that holds the first four days of the year as the first week. |
| vbFirstFullWeek | Sets the first full week of the year as the first week. |
⧭ Return Value:
Returns the part of the given date specified by the given interval.
1. Extracting the Day from a Given Date Using the DatePart Function of VBA
First of all, let’s return the day from a given date using the DatePart function. You have to use the interval “d” for this purpose.
The line of code to extract the day will be:
Day_Number = DatePart("d", "20/11/2019")
Run this code and it’ll return the day of the date 20/11/2019. It’s 20.
Read More: How to Get the Current Date in VBA
2. Finding out the Month of a Given Date Using the DatePart Function of VBA
Now we’ll return the month from a given date using the DatePart function. You have to use the interval “m” for this purpose.
The line of code will be:
Month_Number = DatePart("m", "20/11/2019")
Run this code and it’ll return the month of the date 20/11/2019. It’s 11.
Read More: How to Use EoMonth in Excel VBA
3. Separating the Year from a Given Date Using the DatePart Function of VBA
Next, we’ll return the year from a given date using the DatePart function. You have to use the interval “yyyy” for this purpose.
The line of code will be:
Year_Number = DatePart("yyyy", "20/11/2019")
Run this code and it’ll return the year of the date 20/11/2019. It’s 2019.
Read More: How to Use Year Function in Excel VBA
4. Finding out the Quarter of a Given Date Using the DatePart Function of VBA
Now we’ll return the quarter from a given date using the DatePart function. You have to use the interval “q” for this purpose.
The line of code will be:
Quarter_Number = DatePart("q", "20/11/2019")
Run this code and it’ll return the quarter of the date 20/11/2019. It’s 4.
Read More: How to Get the Day of Week Using VBA
Similar Readings
- Use VBA DateSerial Function in Excel (5 Easy Applications)
- How to Use Excel VBA MONTH Function (7 Suitable Examples)
- Use VBA DateValue Function in Excel (6 Examples)
- How to Use the VBA DateAdd Function in Excel
- Now and Format Functions in Excel VBA (4 Examples)
5. Finding out the Day of Year of a Given Date Using the DatePart Function of VBA
Now we’ll perform a new thing. We’ll return the day number of the year of a given date. We have to use the interval “y” for this purpose.
The line of code will be:
Day_of_the_Year = DatePart("y", "20/11/2019")
Run this code and it’ll return the day of the year of the date 20/11/2019. It’s 324.
Read More: How to Sort Dates in Excel by Year
6. Extracting out the Weekday of a Given Date Using the DatePart Function of VBA
We can also extract out the weekday of a given date using the DatePart function of VBA.
But to accomplish this, you have to specify the first day of the week. It’s optional. Default is Sunday.
Let’s find out the weekday of the date 20/11/2019 considering Saturday as the first day. The interval will be “w” and the first day of the week will be vbSaturday.
The line of code will be:
Weekday_Number = DatePart("w", "20/11/2019",vbSaturday)
Run this code and it’ll return the weekday of the date 20/11/2019 considering Saturday as the first day of the week.
It’s 5. That means it was Wednesday.
7. Extracting out the Number of Weeks of a Given Date Using the DatePart Function of VBA
Finally, we’ll find out the number of the week of a given date using the DatePart function. You have to use the interval “ww” for this purpose.
The number of weeks is counted considering the week with January 1 as the first week by default. If you want to change it, you have to set the argument FirstweekofYear according to table 4 of this article.
Let’s find out the week number of the date 20/11/2019 considering the week with January 1 as the first week (default). Then the line of code will be:
Week_Number = DatePart("ww", "20/11/2019")
Run this code and it’ll return the week number of the date 20/11/2019 considering the week with January 1 as the first week.
It’s 47.
Read More: Excel VBA to Find Week Number (6 Quick Examples)
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
In short, the DatePart function takes a date and returns a specific portion of the date. It can be the day, month, year, day of the year, weekday, week number, etc. It can also return the hour, minute, and second, if there is time attached to the date. Therefore, using these methods you can use the DatePart function of VBA to return a particular portion of any date according to your need. Do you have any questions? Feel free to ask us.
