VBA DatePart

DatePart in VBA is used to identify the part of the date for the given date provided as an argument. The date part can be either days or months or a year or even the hour, minutes, and seconds. The syntax of this function very much clarifies itself. It is as follows: Datepart ( Interval As String, Date).

Syntax

The syntax of the DatePart function is given below:

• Interval: The data to be passed in an interval argument is a string type, meaning this argument can contain any valid values. The interval may be a year, month, quarter, day, week, hour, minute, or second.
• Date: The date value that needs to be evaluated.
• firstdayofweek: This is an optional parameter. It describes the first day of the week. We can even ignore this. If we ignore this parameter, this automatically takes Sunday as the first day of the week. If you wish to change that, you can use this parameter. This argument may consist of vbUseSystem 0.

Use the NLS API setting

vbSunday ( Default) , vbMonday, vbTuesday, vbWednesday, vbThursday vbFriday , vbSaturday.

• firstweekofyear: Likewise, the top parameter is optional. It describes the first week of the year. One can ignore this parameter. It assumes that January 1st is the year's first week if ignored. If you want to change that, you can use this parameter.
  vbUseSystem , vbFirstJan1 , vbFirstFourDays , vbFirstFullWeek.

After giving all the parameters, DatePart () will return the numeric value such as the whole date, year, month, quarter, etc. Hence, the return type of this function will be a numeric value.

Example #1

The first example is to display the month's completion date and quarter.

To achieve this, we need to write some code in Visual Basic. Go to the Developer Tab and click "Visual Basic," and a window will open.

In that window, write the code as shown below.

Code:

Sub date_Datepart()
Dim mydate As Variant

mydate = #12/25/2019#
MsgBox mydate

MsgBox DatePart("q", mydate) 'displays quarter

End Sub

In this example, we used the DatePart function to display the date and date's some part that is a quarter of the date. This displays which quarter of the year is the date coming into.

If we debug the code, the date will be displayed as a complete date the first time the code executes "MsgBox mydate" because we assigned the random date to the "mydate" variable.

Next, we display which quarter of the year that date comes under.

When we run the code manually or using shortcut key F5, the date would display after clicking on "OK." Next, the quarter of the date would be displayed. For example, we can show this in the below screenshot.

Likewise, a quarter can display only the date, month, or year.

Example #2

In this example, we will enter the date manually at the run time.

Code:

Sub date1_datePart()

Dim TodayDate As Date ' Declare variables.
Dim Msg
TodayDate = InputBox("Enter a date:")
Msg = "Quarter: " & DatePart("q", TodayDate)
MsgBox Msg

End Sub

Here in this example, we are trying to get the date manually at run time. So, the code "TodayDate = InputBox("Enter a date:")" indicates that we can enter the date manually.

After entering the date manually, it displays the quarter of the date in a message box. For example, we can show this in the below screenshot.

As June month is in the second quarter, it displays the 2nd quarter, as shown in the above screenshot.

Example #3

It will fill up all the values in the cells in this example.

Code:

Private Sub Workbook_Open()

Dim DummyDate As Date

DummyDate = ActiveSheet.Cells(2, 2)

ActiveSheet.Cells(2, 2).Value = Day(DummyDate)
ActiveSheet.Cells(3, 2).Value = Hour(DummyDate)
ActiveSheet.Cells(4, 2).Value = Minute(DummyDate)
ActiveSheet.Cells(5, 2).Value = Month(DummyDate)
ActiveSheet.Cells(6, 2).Value = Weekday(DummyDate)

End Sub

We can fill the dates in the cells in the Excel sheet. For that, we can write the code as "ActiveSheet.Cells." By this code, we can insert the present date, maybe a year, month, or date, into the given cells.

For example, in the above screenshot,

The day will be inserted in the Excel sheet cells ( 2, 2). Hence, the code is written as "ActiveSheet.Cells(2, 2).Value = Day(DummyDate)."

Run the code using the F5 key or manually. The result would be as shown below.

It is, by default, taking the date today and displaying it as 30 in (2,6) cell.

Likewise, for all the other data also, we can fill it.

• We can use the DatePart function to display the part of the date as the name indicates, i.e., if only the day, month, or year of the date needs to be displayed, then we can use this function.
• This function also separates a date, month, and year from a particular date.
• By using this function, the date is not only separated. We can also get the quarter, day, hour, minute, and second.

• We can only use this function as a VBA Function. In normal Excel, we cannot use this.
• We can give the dates given as a value in this function in any format such as mm-dd-yyyy format or DD-MM-YYYY format etc.
• This function will separate all the values, such as date, month, year, or time, an hour, minute, and seconds.
• It is organized under Date and Time functions in VBA of Microsoft Excel.