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.

Syntax of DatePart in Excel VBA

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.

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.

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

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

Code:

Sub Sample()

End Sub

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

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

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

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

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

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.

Step 10: Enter the Date Part value.

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

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

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

Step 3: Provide a date to the variable.

Code:

Sub Sample1()

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

End Sub

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

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

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

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.

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

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

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

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

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

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

The current date is 25^th of July which is 30^th 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