VBA DATEPART Function

VBA DATEPART Function

Objective of the VBA DATEPART Function:

The VBA DATEPART Function 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.

Table of Contents:

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.

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.
VBA DATEPART Function Example1

 

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.
VBA DATEPART Function Example2

 

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.
VBA DATEPART Function Example3

Instructions

Here are the instructions to use the VBA DATEPART Function in Excel

  1. Open an Excel workbook.
  2. Press Alt+F11 to open VBA Editor window.
  3. Go to Insert menu and click on module from the available options.
  4. Insert above specified macro example.
  5. Select code and click on ‘Run’ command or use Keyboard shortcut ‘F5’.
  6. Ex1-Output: It displays current system time on the screen.
  7. Ex2-Output: It extracts Year, Month and Day from a Date and Display on the Worksheet at Range B18 to B20.
  8. Ex3-Output: It extracts Hours, Minutes and Seconds from a Time and Display on the screen

Download File

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

VBA DATEPART Function Examples

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.
List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *