How to Use the VBA DatePart Function in Excel (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Quick View of the VBA Datepart Function

It’ll return 20, the day of the date “20/11/2019”.

Output of Using the VBA Datepart Function


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:

Syntax of the VBA Datepart Function

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.

Returning Day by the VBA Datepart Function

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.

Returning Month by the VBA Datepart Function

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")

VBA Code to Return Year by the VBA Datepart Function

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")

VBA Code to Return the Quarter by the VBA Datepart Function

Run this code and it’ll return the quarter of the date 20/11/2019. It’s 4.

Returning the Quarter by the VBA Datepart Function

Read More: How to Get the Day of Week Using VBA


Similar Readings


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.

Returning the Day of the Year by the VBA Datepart Function

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)

VBA Code to Return the Weekday by the VBA Datepart Function

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.

Returning Week Number of the Year by the VBA Datepart Function

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.


Related Articles

Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo