How to use DatePart function in VBA?

The DatePart function in VBA (Visual Basic for Applications) is used to return a specific part of a given date, such as the year, month, day, hour, minute, etc.

The syntax for the DatePart function is:


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


  • interval is a string expression that represents the part of the date/time value you want to return.
  • date is the date or datetime expression you want to evaluate.
  • firstdayofweek (optional) specifies the first day of the week and can be set to any constant from vbSunday to vbSaturday. If omitted, vbSunday is assumed.
  • firstweekofyear (optional) specifies the first week of the year and can be set to one of the constants: vbFirstJan1, vbFirstFourDays, or vbFirstFullWeek. If omitted, vbFirstJan1 is assumed.

Here’s how to use the DatePart function with different intervals:


Sub DatePartExamples()

    Dim d As Date
    d = Now
    ' Retrieve the year from the date
    Debug.Print "Year: " & DatePart("yyyy", d)
    ' Retrieve the month from the date
    Debug.Print "Month: " & DatePart("m", d)
    ' Retrieve the day of the month from the date
    Debug.Print "Day: " & DatePart("d", d)
    ' Retrieve the hour from the date
    Debug.Print "Hour: " & DatePart("h", d)
    ' Retrieve the minute from the date
    Debug.Print "Minute: " & DatePart("n", d)
    ' Retrieve the second from the date
    Debug.Print "Second: " & DatePart("s", d)
    ' Retrieve the day of the week from the date
    Debug.Print "Weekday: " & DatePart("w", d)
    ' Retrieve the day of the year from the date
    Debug.Print "DayOfYear: " & DatePart("y", d)
    ' Retrieve the week of the year from the date
    Debug.Print "WeekOfYear: " & DatePart("ww", d)
End Sub

In this example, various parts of the current date and time (Now) are printed to the Immediate Window (which can be viewed by pressing Ctrl+G in the VBA editor).

The interval parameter accepts several predefined string literals to represent different date/time parts. Some of the common ones include:

  • “yyyy” for year
  • “q” for quarter
  • “m” for month
  • “y” for day of year
  • “d” for day
  • “w” for weekday
  • “ww” for week of year
  • “h” for hour
  • “n” for minute
  • “s” for second

Make sure you’re using the correct interval string for the part of the date/time you’re interested in.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

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