VBA DATEPART Function

HomeVBA Functions LIST (Category Wise)How to use the VBA DATEPART Function (Syntax + Example)

What is VBA DATEPART Function

The VBA DATEPART function is listed under the date category of VBA functions. When you use it in a VBA code, it can return the day value from a date or a time value. In simple words, you can get seconds, minutes, hours, days, months, or years from a date or a time value.

how to use it

To use VBA's DATEPART function you need to understand its syntax and arguments:

Syntax

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

Arguments

  • Interval: A string to specify which part of the date need to return in the result.
  • Date: The date from which the date part needs to return.
  • [FirstDayOfWeek]: A string to define the first day of the week [This is an optional argument and if omitted vbSunday by default].
    • vbUseSystemDayOfWeek - As per the system settings.
    • vbSunday - Sunday
    • vbMonday - Monday
    • vbTuesday - Tuesday
    • vbWednesday - Wednesday
    • vbThursday - Thursday
    • vbFriday - Friday
    • vbSaturday - Saturday
  • [FirstWeekOfYear]: A string to define the first week of the year [This is an optional argument and if omitted vbFirstJan1 by default].
    • vbSystem - As per the system settings.
    • vbFirstJan1 - The week in which the 1st Day of Jan occurs.
    • vbFirstFourDays - The first week that contains at least four days in the new year.
    • vbFirstFullWeek - The first full week in the new year.

Example to use DATEPART Function in VBA

To practically understand how to use VBA DATEPART function, you need to go through the below example where we have written a vba code by using it:

Sub example_DATEPART()
Range("A2").Value = DatePart("d", Range("A1"))
Range("A3").Value = DatePart("h", Range("A1"))
Range("A4").Value = DatePart("m", Range("A1"))
Range("A5").Value = DatePart("n", Range("A1"))
Range("A6").Value = DatePart("q", Range("A1"))
Range("A7").Value = DatePart("s", Range("A1"))
Range("A8").Value = DatePart("w", Range("A1"))
Range("A9").Value = DatePart("ww", Range("A1"))
Range("A11").Value = DatePart("y", Range("A1"))
Range("A12").Value = DatePart("yyyy", Range("A1"))
End Sub

In the above code, we have used different interval in DATEPART:

  • "d" to get the day and it has returned 15.
  • "h" to get the hours but as we don’t have time in the date it has returned 0 in the result.
  • "m" to get the month and it has returned 1.
  • "n" to get the minutes but as we don’t have time in the date it has returned 0 in the result.
  • "q" to get the quarter and it has returned 1.
  • "s" to get the seconds but as we don’t have time in the date it has returned 0 in the result.
  • "w" to get the day of the week and it has returned 3.
  • "ww" to get the week of the year and it has returned 3.
  • "y" to get the day of the year and it has returned 15.
  • "yyyy" to get the year and it has returned to 2019.

Notes

Below are some important points which you need to take care while using DATEPART function in VBA.

  • If a date is supplied without the year then the current year will be used.
  • If the value specified is a value other than a date or a date that can't be recognized as a date, VBA will return the run-time 13 error.
  • Defining the "firstdayofweek" argument works only when the "interval" is either "w" or "ww".

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.