EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Date & Time Functions VBA DatePart
Secondary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date

VBA DatePart

By Madhuri ThakurMadhuri Thakur

VBA DatePart

Excel VBA DatePart

Suppose we have dates in our data and we don’t know that either which week or which part of the month it is. There are various ways to find this out. In excel there are some complex formulas which we need to perform in order to find that. For example, if there is a date, 02-Feb-2019, the calculation to find out the quarter for this date in excel is complex. However, VBA provides us a very handy function called DatePart which performs such calculations for us.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

I have explained above now why we use DatePart function in VBA. This function is used to find out the part of the date, be it a day, month week quarter or even hours or seconds. This function returns the integer as an output. To understand more about this function let us first go through the syntax of this function in detail.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of DatePart in Excel VBA

Let us look at the below syntax for DatePart in Excel VBA.

Syntax of VBA DatePart

As we can see there is a total of four arguments provided to this function. The first two arguments are mandatory while the rest two arguments are optional. However, if we do not provide the last two arguments VBA has its own default values for it.

  • Interval: This argument is provided as a string to this function. This argument can be a month, week, year, Day or even hour or minutes or seconds. For example, for a quarter the syntax will be “q”, or for the year the syntax will be “yyyy” and so on.
  • Date: This is the input date, we provide to this function for whose part we want to find out.
  • FirstDayofWeek: This is an optional argument which we give to this function. But if we do not provide the firstdayofweek to the function, VBA automatically treats SUNDAY as the first day of the week. There are different syntax for providing this argument which is as follows:

vbUseSystem (This syntax uses NLS API SETTING), vbSunday (This is the default argument), vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, vbSaturday (Rest all these arguments are optional to be given)

  • FirstWeekofYear: This is also an optional argument for this function. But again if we do not provide this argument VBA automatically considers the first week whichever week is the first from January 1. Also for this argument, there are different syntaxes, they are as follows:

vbUseSystem, vbFirstJan1 (This is the default), vbFirstFourDays (This argument starts with the first week which has at least the first four days in the new year), vbFirstFullWeek (This argument starts with the first full week of the year).

Now we have learned about the functions and its arguments let us test this function in examples.

How to Use the DatePart Function in Excel VBA?

Below are the different examples to use DatePart function in excel using VBA code.

You can download this VBA DatePart Excel Template here – VBA DatePart Excel Template

Excel VBA DatePart – Example #1

Let us take an input from the user as date and part which the user wants to find out, then we will use the DatePart function and display the result.

Follow the below steps to use DatePart Function in Excel VBA:

Step 1: From the developer’s tab in the code sectio   n click on Visual basic which will take us to the VB Editor.

VBA DatePart Example 1-1

Step 2: Once in the VB Editor, insert a new module in the VBA project as shown below.

VBA DatePart Example 1-2

Step 3: Now double click on the module and start declaring a new subprocedure as shown below.

Code:

Sub Sample()

End Sub

VBA DatePart Example 1-3

Step 4: Define three variables one as String and date which will hold the date part and date of our input and another as an integer which will store our output.

Code:

Sub Sample()

Dim Dt As Date, Prt As String, Res As Integer

End Sub

VBA DatePart Example 1-4

Step 5: In the date variable ask the input from the user for the input date.

Code:

Sub Sample()

Dim Dt As Date, Prt As String, Res As Integer
Dt = InputBox("Enter a Date")

End Sub

VBA DatePart Example 1-5

Step 6: In Prt which is part take input from the user for the date part we need to find out.

Code:

Sub Sample()

Dim Dt As Date, Prt As String, Res As Integer
Dt = InputBox("Enter a Date")
Prt = InputBox("Enter Date Part")

End Sub

VBA DatePart Example 1-6

Step 7: Now in Variable Res which is result let us find out the result using the DatePart function as follows.

Code:

Sub Sample()

Dim Dt As Date, Prt As String, Res As Integer
Dt = InputBox("Enter a Date")
Prt = InputBox("Enter Date Part")
Res = DatePart(Prt, Dt)

End Sub

VBA DatePart Example 1-7

Step 8: Display the value stored in Res using the msgbox function.

Code:

Sub Sample()

Dim Dt As Date, Prt As String, Res As Integer
Dt = InputBox("Enter a Date")
Prt = InputBox("Enter Date Part")
Res = DatePart(Prt, Dt)
MsgBox Res

End Sub

VBA DatePart Example 1-8

Step 9: Now let us run the above code by pressing the F5 key or by clicking on the Play button and provide a date as follows.

VBA DatePart Example 1-9

Step 10: Enter the Date Part value.

VBA DatePart Example 1-10

Step 11: Press OK to see the result as shown below.

VBA DatePart Example 1-11

We get 1 as a result as the date we provided as input falls in the first quarter of the year.

Excel VBA DatePart – Example #2

This time let us provide the date directly to the function in the code. Follow the below steps to use DatePart Function in Excel VBA:

Step 1: In the same module define another subprocedure as shown below.

Code:

Sub Sample1()

End Sub

VBA DatePart Example 2-1

Step 2: Define one variable as Integer and one variable as Date as shown below.

Code:

Sub Sample1()

Dim Dt As Date, Res As Integer

End Sub

VBA DatePart Example 2-2

Step 3: Provide a date to the variable.

Code:

Sub Sample1()

Dim Dt As Date, Res As Integer
Dt = #2/2/2019#

End Sub

Provide Date Example 2-3

Step 4: In the Res variable, let us calculate the week of the date using the DatePart function.

Code:

Sub Sample1()

Dim Dt As Date, Res As Integer
Dt = #2/2/2019#
Res = DatePart("ww", Dt)

End Sub

Week of the date Example 2-4

Step 5: Display the value stored in the variable using the msgbox function.

Code:

Sub Sample1()

Dim Dt As Date, Res As Integer
Dt = #2/2/2019#
Res = DatePart("ww", Dt)
MsgBox Res

End Sub

Message Box Example 2-5

Step 6: Run the above code by pressing the F5 key or by clicking on the Play button to get the following result.

VBA DatePart Example 2-6

The date we provided as input falls in the fifth week of the year.

Excel VBA DatePart – Example #3

Let us try some real-life example. In sheet 1 in cell A1, I have used the formula =Now() to get the current date and time and we will find out the date part for this cell.

NOW Formula Example 3-1

Follow the below steps to use DatePart Function in Excel VBA:

Step 1: In the same module, define another subprocedure as follows.

Code:

Sub Sample2()

End Sub

VBA DatePart Example 3-2

Step 2: Define two variable one as Date and another as an integer.

Code:

Sub Sample2()

Dim Dt As Date, Res As Integer

End Sub

VBA DatePart Example 3-3

Step 3: In variable Dt store the value of cell A1 is as follows.

Code:

Sub Sample2()

Dim Dt As Date, Res As Integer
Dt = Range("A1").Value

End Sub

Store Value Example 3-4

Step 4: Now in res variable let us calculate the week part for the input using the DatePart function.

Code:

Sub Sample2()

Dim Dt As Date, Res As Integer
Dt = Range("A1").Value
Res = DatePart("ww", Dt)

End Sub

Calculate week part Example 3-5

Step 5: Display the value stored in the variable using the Msgbox function.

Code:

Sub Sample2()

Dim Dt As Date, Res As Integer
Dt = Range("A1").Value
Res = DatePart("ww", Dt)
MsgBox Res

End Sub

Message Box Example 3-6

Step 6: Run the above code by pressing the F5 key or by clicking on the Play button to get the following result.

Result of VBA Example 3-7

The current date is 25th of July which is 30th week of the year 2019.

Things to Remember

  • It is date function but returns an integer value.
  • The first two arguments are mandatory while the rest two arguments are optional.
  • VBA has its own default arguments if we do not provide the last two arguments to DatePart function.
  • This function is a VBA function and not a worksheet function.

Recommended Articles

This is a guide to VBA DatePart. Here we discuss how to use DatePart function in Excel using VBA code along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA DateAdd
  2. VBA Date
  3. VBA DateDiff
  4. VBA Date Format
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more