Date functions in VBA

This page gives an overview of the VBA Date functions with example code showing how they are used. A link is provided for each function to its documentation on microsoft learn VBA site.

Below image shows the Code VBA add-in support for VBA date procedures.

menu with date functions.

Hovering a menu you see an explanation what a procedure does, and what the inserted code will look like, depending on changes you make in the Function Call Builder dialog. Note that Code VBA IntelliSense also makes these function accessible under submenu VBA.

Date - Returns the current system date

The Date function returns a Date containing the current system date.


Dim dtToday As Date: dtToday = Date 'returns the current system date.'

DateValue - Converts the input to a Date

The DateValue function returns a Date.


dt = DateValue(Date:="February 12, 2023") ' Returns 2/12/2023 '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/datevalue-function

DateSerial - Returns a Date for a specified year, month, and day.

The DateSerial function returns a Date for a specified year, month, and day.


dt = DateSerial(2023, 2, 12)  ' Returns 2/12/2023 '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/dateserial-function

FormatDateTime - Formatted string for a given a date

The FormatDateTime function returns an expression formatted as a date or time


strFormattedDateTime = FormatDateTime(Expression:="February 12, 2023") 'returns 2/12/2023'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatdatetime-function

Format - Specifying non standard values

With the Format function use standard localized date formats, or you can devise your own Date formatted string.

Without explicit formatting argument: Format 


strFormatted = Format(Expression:="February 12, 2023") 'returns 12/02/2023, (Short date)'

Using standard localized date formats:


strFormatted = Format(Expression:="February 12, 2023", Format:="Long date") 'returns Sunday, 12 February 2023'
strFormatted = Format(Expression:="February 12, 2023", Format:="Medium Date") 'returns 12-Feb-23'

...your own Date formatted string by combining any from below elements.


strFormatted = Format(Expression:="February 12, 2023", Format:="dddd, mmm d yyyy") 'returns Sunday, Feb 12 2023'
Symbol Range
d 1-31 (Day of month, with no leading zero)
dd 01-31 (Day of month, with a leading zero)
w 1-7 (Day of week, starting with Sunday = 1)
ww 1-53 (Week of year, with no leading zero; Week 1 starts on Jan 1)
m 1-12 (Month of year, with no leading zero, starting with January = 1)
mm 01-12 (Month of year, with a leading zero, starting with January = 01)
mmm Displays abbreviated month names (Hijri month names have no abbreviations)
mmmm Displays full month names
y 1-366 (Day of year)
yy 00-99 (Last two digits of year)
yyyy 100-9999 (Three- or Four-digit year)

Day - Returns an Integer representing the day of the month

The Day function returns an Integer specifying a whole number between 1 and 31, inclusive, representing the day of the month.


iDay = Day(Date:="February 12, 2023") 'returns 12'

WeekDay - Returns an Integer representing the day of the week

The WeekDay function returns a Integer containing a whole number representing the day of the week. This function has the optional argument 'firstdayofweek', if not specified, vbSunday is assumed as first day.


iWeekday = Weekday(Date:="February 12, 2023") 'returns 1 (= Sunday)'

See https://learn.microsoft.com/office/vba/language/reference/user-interface-help/weekday-function

WeekdayName - Name the specified day of the week

The WeekdayName function returns a string indicating the specified day of the week. This function has the optional argument 'firstdayofweek', if not specified, vbSunday is assumed as first day.


strWeekdayName = WeekdayName(Weekday:=4)'returns Wednesday'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/weekdayname-function

Month - Returns an Integer representing the month of the year

The Month function returns a Integer specifying a whole number between 1 and 12, inclusive, representing the month of the year.


iMonth = Month(Date:="February 12, 2023") 'returns 2'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/month-function

Year - Returns a number representing the year

The Year function returns a Integer containing a whole number representing the year.


iYear = Year(Date:="February 12, 2023") 'returns 2023'

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/year-function

DateAdd - Returns a Date containing a date to which a specified time interval has been added.

The DateAdd function returns a Date containing a date to which a specified time interval has been added. As interval you can use Year ("yyyy"), Day ("d"), or Month ("m"). To add days to date, you also can use Day of Year ("y") or Weekday ("w").


dt = DateAdd(Interval:="m", Number:=4, Date:="February 12, 2023") ' returns 6/12/2023 '
dt = DateAdd(Interval:="yyyy", Number:=4, Date:="February 12, 2023") ' returns 2/12/2027 '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/dateadd-function

DateDiff - Returns the number of time intervals between two dates.

The DateDiff function returns a Long specifying the number of specified time intervals between two specified dates. As interval you can use a.o. Year ("yyyy"), Day ("d"), Month ("m"), Week("ww"). There are also settings for the firstdayofweek, default = Sunday, and the firstweekofyear, default = the week in which January 1 occurs.


lng = DateDiff(Interval:="yyyy", Date1:="February 12, 2023", Date2:="February 23, 2043") 'returns 20 (year) '
lng = DateDiff(Interval:="ww", Date1:="February 12, 2023", Date2:="April 23, 2023") 'returns 10 (weeks) '
lng = DateDiff(Interval:="d", Date1:="February 12, 2023", Date2:="April 23, 2023") 'returns 70 (days) '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/datediff-function

DatePart - Returns a number for the specified part of a given date

The DatePart function returns a Variant (Integer) containing the specified part (day, month, quarter, year) of a given date. There is an issue with the use of this function. The last Monday in some calendar years can be returned as week 53 when it should be week 1. For more information and a workaround, see Format or DatePart functions can return wrong week number for last Monday in Year. 


i = DatePart(Interval:="m", Date:="February 12, 2023") 'returns 2'
i = DatePart(Interval:="d", Date:="February 12, 2023") 'returns 12'
i = DatePart(Interval:="yyyy", Date:="February 12, 2023") 'returns 2023'
i = DatePart(Interval:="q", Date:="February 12, 2023") 'returns (quarter=) 1 '

See https://docs.microsoft.com/office/vba/language/reference/user-interface-help/datepart-function

MonthName - Name of the specified month

The MonthName function returns a string indicating the specified month.


strMonthName = MonthName(Month:=12) 'returns "December" '
strMonthName = MonthName(Month:=12, abbreviate:=True) 'returns "Dec"'