Excel VBA DatePart Function
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 year or even the hour minutes and seconds, the syntax of this function very much clarifies itself and it is as follows, Datepart ( Interval, Date as Argument).
In Microsoft Excel, DatePart is a VBA Function that returns a specified part of a given date. As the name describes the function will return some part of the date which is specified in the function arguments. It is a built-in function and is categorized under Date and Time functions, but this is generally present in VBA script of Excel. In normal Excel functions, this function is not seen.
The syntax of the DatePart function is given below:
- Interval: The data to be passed in an interval argument is string type which means this argument can contain any valid values in it. The interval may be of year, month, quarter, day, week, hour, minute, second.
- Date: The date value that needs to be evaluated.
- firstdayofweek: This is an optional parameter. This describes the first day of the week, this can even be ignored. If this parameter is ignored this automatically takes Sunday as the first day of the week. If you would wish to change that then this parameter can be used. 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, this is also an optional parameter. This describes the first week of the year. This parameter can also be ignored. If this parameter is ignored, it assumes that January 1st as the first week of the year. If you would like to change that then this parameter can be used.
This argument may consist of the following values.
vbUseSystem , vbFirstJan1 , vbFirstFourDays , vbFirstFullWeek.
After giving all the parameters, Datepart () will return the numeric value such as whole date or year or month or quarter, etc. Hence the return type of this function will be a numeric value.
How to use DatePart Function in VBA?
The First Example is to display the complete date and the quarter of that month as well.
In that window write the code as shown below.
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 have used Datepart function to display the date and some part of the date 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 complete date first time when the code executes “Msgbox mydate” because the random date is assigned to “mydate” variable.
Next, we are displaying which quarter of the year that date comes under.
When you run the code manually or using shortcut key F5 then, the date would be displayed as shown after clicking on ok. Next, the quarter of the date would be displayed this can shown in below screenshot.
Likewise, Quarter, only date or month or year can also be displayed.
In this example, I 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. The code “ TodayDate = InputBox(“Enter a date:”)” this line indicates that the date can be entered manually,
After entering the date manually it displays the Quarter of the Date in a message Box. This can be shown in below screenshot.
As the June month is in the 2nd quarter, this displays the 2nd Quarter as shown in the above screenshot.
In this example, all the values will be filled up in the cells.
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
The dates are filled in the cells in the excel sheet, for that the code is written as Active Sheet.cells. By this code the date which is present may be year month or date can be inserted into the given cells.
For example, in the above screenshot,
The day is to be inserted in the cells ( 2, 2) of the excel sheet. Hence the code is written as “ ActiveSheet.Cells(2, 2).Value = Day(DummyDate) “ .
Run the code using the F5 key or manually and the result would be as shown below.
It is by default taking today date and it is displaying as 30 in (2,6) cell.
Likewise for all the other data also it can be filled.
Usage of DatePart Function
VBA DatePart function can be used to display the part of the date as the name indicates i.e., if only day or month or year of the date needs to be displayed then this function can be used. This function also separates date, month and a 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 a second.
Things to Remember
- This function can only be used as a VBA Function. In normal Excel, this cannot be used.
- The dates which are given as a value in this function can be given in any format such as mm-dd-yyyy format or DD-MM-YYYY format etc.
- This function will separate all the values separately such as date, month, year or time also an hour, minute, seconds also.
- This is organized under Date and Time Functions in VBA of Microsoft Excel.
This has been a guide to VBA DatePart. Here we learn how to use DatePart function in Excel VBA to return specified part of date along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –