VBA DATEPART Function in Excel

The VBA DATEPART Function in Excel is a built-in function in MS Excel. It returns a specified part of a supplied date or time. It has four input arguments or parameters. In these four arguments first two parameters are mandatory and last two parameters are optional. It is a ‘Data and Time’ type function.

The VBA DATEPART Function can be used in either procedure or function in a VBA editor window in Excel. We can use the VBA DATEPART Function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the DATEPART Function, where we can use this VBA DATEPART Function and real-time examples.

Syntax of VBA DATEPART Function

The syntax of the VBA DATEPART Function is

DATEPART (interval, date, [firstdayofweek], [firstweekofyear])

In the above syntax first two arguments are mandatory (Interval & date). And the last two parameters are optional (firstdayofweek & firstweekofyear)

Parameters or Arguments:

Where
interval: The interval is a mandatory argument. It represents interval of date or time which will be returned for a given date or time. It accepts string type input value. Here are the different interval types, which are shown in the below table.

Interval Type	Description
YYYY	Year
Y	Day of Year
Q	Quarter
M	Month
WW	Week
W	Weekday
D	Day
H	Hour
N	Minute
s	Second

 

Date: Date is a mandatory argument. The Date or time value for which the interval part is to be returned.
Firstdayofweek: The firstdayofweek is an optional parameter. If this parameter excluded, default it considers ‘Sunday’ is the first day of week. Here are the different VBA Constants, which are shown in the below table.

Value	Constant	Description
0	vbUseSystem	uses the system settings
1	vbSunday	Sunday (default)
2	vbMonday	Monday
3	vbTuesday	Tuesday
4	vbWednesday	Wednesday
5	vbThursday	Thursday
6	vbFriday	Friday
7	vbSaturday	Saturday

Firstweekofyear: The firstweekofyear is an optional parameter. If this parameter excluded, default it considers ‘1st January’ is the first week of the year. Here are the different VBA Constants, which are shown in the below table.

Value	Constant	Description
0	vbUseSystem	uses the system settings
1	vbFirstJan1	Use the first week that includes Jan 1st (default)
2	vbFirstFourDays	Use the first week in the year that has at least 4 days
3	vbFirstFullWeek	Use the first full week of the year

Where we can apply or use the VBA DATEPART Function?

We can use this VBA DATEPART function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Extract Year, Month and Day from a Date and display on the screen

Here is a simple example of the VBA DATEPART Function. This below example macro uses the DATEPART Function and extracts Year. Month and Day from a date and then it will display on the screen.

Sub VBA_DATEPART_Function_Example1()
'Extract Year, Month and Day from a Date and Display on the screen
    
    'Variable declaration
    Dim iCurrentYear As Integer
    Dim iCurrentMonth As Integer
    Dim iCurrentDay As Integer
    
    'Assign current system Year, Month and Day to multiple variables
    iCurrentYear = DatePart("yyyy", Date)
    iCurrentMonth = DatePart("M", Date)
    iCurrentDay = DatePart("D", Date)
    
    'Display output on the screen
    MsgBox "Current Year: " & iCurrentYear & vbCr & "Current Month: " & iCurrentMonth & vbCr & "Current Day: " & iCurrentDay
    
End Sub

In the above example ‘sCurrentTime’ declared as a Time data type. This variable ‘sCurrentTime’ now contains the current system time.
Output:
Here is the screen shot of first example output.

 

Example 2: Extract Year, Month and Day from a Date and Display on the Worksheet

Here is a simple example of the VBA DATEPART Function. This below example macro uses the DATEPART Function and extracts Year. Month and Day from a date and then it will display on the Worksheet. Here Worksheet name is ‘VBAF1.COM’.

Sub VBA_DATEPART_Function_Example2()
'Extract Year, Month and Day from a Date and Display on the Worksheet

    'Variable declaration
    Dim iCurrentYear As Integer
    Dim iCurrentMonth As Integer
    Dim iCurrentDay As Integer
    
    'Assign current system Year, Month and Day to multiple variables
    iCurrentYear = DatePart("yyyy", Date)
    iCurrentMonth = DatePart("M", Date)
    iCurrentDay = DatePart("D", Date)
    
    'Display output on the worksheet
    With Sheets("Sheet1")
        .Range("B18") = "Current Year: " & iCurrentYear
        .Range("B19") = "Current Month: " & iCurrentMonth
        .Range("B20") = "Current Day: " & iCurrentDay
    End With
    
End Sub

Output:
Here is the screen shot of second example output.

 

Example 3: Extract Hours, Minutes and Seconds from a Time and Display on the screen

Here is a simple example of the VBA DATEPART Function. This below example macro uses the DATEPART Function and extracts Hours. Minutes and Seconds from a time and then it will display on the screen. In the below example, Now is a VBA function. It returns current system date and time.

Sub VBA_DATEPART_Function_Example3()
'Extract Hours, Minutes and Seconds from a Time and Display on the screen

    'Variable declaration
    Dim iCurrentHour As Integer
    Dim iCurrentMinute As Integer
    Dim iCurrentSecond As Integer
    
    'Assign current Hours,Minutes and Seconds to multiple variables
    iCurrentHour = DatePart("H", Now)
    iCurrentMinute = DatePart("N", Now)
    iCurrentSecond = DatePart("S", Now)
    
    'Display output on the screen
    MsgBox "Current Hours: " & iCurrentHour & vbCr & "Current Minutes: " & iCurrentMinute & vbCr & "Current Seconds: " & iCurrentSecond
    
End Sub

Output:
Here is the screen shot of third example output.

Download File

Click on following link to download free example excel workbook to learn more about the VBA DATEPART Function.

VBA DATEPART Function Examples

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers