EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Date & Time Functions VBA DatePart
 

VBA DatePart

Madhuri Thakur
Article byMadhuri 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.

 

 

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA DatePart Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW