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.
- Objective
- Syntax of VBA DATEPART Function
- Parameters or Arguments
- Where we can apply or use the VBA DATEPART Function?
- Example 1: Extract Year, Month and Day from a Date and display on the screen
- Example 2: Extract Year, Month and Day from a Date and display on the Worksheet
- Example 3: Extract Hours, Minutes and Seconds from a Time and display on the screen
- Download File
- Instructions to Run VBA Macro Code
- Other Useful Resources
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