Excel VBA DatePart Function

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA DatePart (wallstreetmojo.com)

How to use DatePart Function in VBA?

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 BasicCode In Visual BasicVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. Go to the Developer TabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more 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.

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.

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

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.

Usage of DatePart Function

Things to Remember

  • We can only use this function as a VBA FunctionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. 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.

This article has been a guide to VBA DatePart. Here, we learn how to use the DatePart function in Excel VBA to return specified parts of the date along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –

  • VBA Wait FunctionDateDiff Function in VBADate Function in VBAVBA DateAdd