How to use the VBA DATEPART Function (Syntax + Example)

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

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.

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 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

  • 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

DATE | DATEADD | DATEDIFF | DATESERIAL | DATEVALUE | DAY | MONTH | MONTHNAME | WEEKDAY | WEEKDAYNAME | YEAR