VBA Date Functions

This tutorial will cover how to work with dates in VBA.

Dates in VBA

In Excel (and VBA), Dates are stored as integer values.

Day 1 in the world of Excel was the 1st January 1900 (Windows default) or 1st January 1904 (Macintosh default) – which means that the 5th August 2021 is day 44413.  44413.456 is 10.56am on the 5th August 2021 where 44413.5 will be exactly 12 noon.

In Excel, DateTimes are then formatted to show as a date.

There are several ways to refer to Dates in VBA:

DateSerial Function

The DateSerial Function returns a date from a provided Year, Month, and Day:

Sub DateSerial_Example()
    MsgBox DateSerial(2019, 4, 1)
End Sub

This code will return 04/01/2019.

You can also enter negative values to move backwards in time:

MsgBox DateSerial(2019, -5, 10)

Result: 07/10/2018

DateValue Function

The DateValue Function returns an integer corresponding to a Date:

Sub DateValue_Example()
    MsgBox DateValue("Feb 12, 2019")
End Sub

This code will return 02/12/2019.

The following examples will have same result.

DateValue("2 12 2019")
DateValue("2/12/2019")
DateValue("2,12,2019")
DateValue("2-12-2019")
DateValue("February 12, 2019")
DateValue("Feb/12/2019")

If date is a string that includes only numbers separated by valid date seperators(” “, “/”, “,”, “-“), DateValue recognizes the order for month, day, and year according to the Short Date format that you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form.

If the year part of date is omitted, DateValue uses the current year from your computer’s system date.

If the date argument includes time information, DateValue doesn’t return it. However, if date includes invalid time information (such as “37:69”), an error occurs.

This example will reference a cell containing a date:

Sub DateValue_ReferenceDates_Cell()
    
    MsgBox DateValue(Range("C2").Value)
    
End Sub

This will return 01/02/2019.

VBA DatePart Function

The DatePart Function works in reverse of the DateSerial Function. It will output a part of a date (year, month, day, etc.) from a date serial:

Sub DatePart_Year_Test()
    MsgBox DatePart("yyyy", #1/1/2019#)
End Sub

This code will return 2019.

The DatePart Function contains 4 arguments:

 

Interval: Time unit (Days, Months, Years, etc.). Enter as string. (ex. “m” for Month)

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of Year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

Date: Varient (Date) value that you want to evaluate.

FirstDayOfWeek: A constant that specifies the first day of the week. This is optional. If not specified, Sunday is assumed.

Constant	Value	Description
vbUseSystem	0	Use the NLS API setting.
vbSunday	1	Sunday (default)
vbMonday	2	Monday
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

FirstWeekOfYear: A constant that specifies the first week of the year. This is optional. If not specified, the first week is assumed to be the week in which January 1 occurs.

Constant	Value	Description
vbUseSystem	0	Use the NLS API setting.
vbFirstJan1	1	Start with week in which January 1 occurs (default).
vbFirstFourDays	2	Start with the first week that has at least four days in the new year.
vbFirstFullWeek	3	Start with first full week of the year.

Referencing Dates

To start, we will demonstrate different ways to reference dates using the VBA DatePart Function.

Each of these DatePart functions produce the same result:

Sub DateAdd_ReferenceDates()

    MsgBox DatePart("yyyy", #4/1/2019#)

    MsgBox DatePart("yyyy", DateSerial(2019, 4, 1))

    MsgBox DatePart("yyyy", DateValue("April 1, 2019"))

End Sub

Or you can reference a cell containing a date:

Sub DatePart_ReferenceDate_Cell()

    MsgBox DatePart("yyyy", Range("C2").Value)
    
End Sub

Or create and reference date variables:

Sub DatePart_Variable()

    Dim dt As Date
    dt = #4/1/2019#

    MsgBox DateAdd("yyyy", dt)

End Sub

 

Using Different Units of Interval

Quarter

Sub DatePart_Quarter()
    MsgBox DatePart("q", #6/30/2019#)
End Sub

Month

Sub DatePart_Month()
    MsgBox DatePart("m", #6/30/2019#)
    ' equivalent
    MsgBox Month(#6/30/2019#)
End Sub

Day

Sub DatePart_Day()
    MsgBox DatePart("d", #6/30/2019#)
    ' equivalent
    MsgBox Day(#6/30/2019#)
End Sub

Week

Sub DatePart_Week_Test()
    MsgBox DatePart("w", #6/30/2019#)
    ' equivalent
    MsgBox Weekday(#6/30/2019#)
End Sub

Hour

Sub DatePart_Hour()
    Dim dt As Date
    Dim nHour As Long
    
    dt = #8/14/2019 9:30:00 AM#
    
    nHour = DatePart("h", dt)

    MsgBox nHour
    ' equivalent
    MsgBox Hour(dt)
End Sub

Minutes

Sub DatePart_Minute()
    MsgBox DatePart("n", #8/14/2019 9:15:00 AM#)
    ' equivalent
    MsgBox Minute(#8/14/2019 9:15:00 AM#)
    MsgBox Minute(#9:15:00 AM#)
End Sub

Second

Sub DatePart_Second()
    MsgBox DatePart("s", #8/14/2019 9:15:15 AM#)
    ' equivalent
    MsgBox Second(#8/14/2019 9:15:15 AM#)
    MsgBox Second(#9:15:15 AM#)
End Sub

VBA Date Function

You can use the Date Function to return the current date.

The syntax of the Date Function is Date(). It has no arguments.

The following code shows you how to use the Date Function:

Sub UsingTheDateFunction()

Dim theDate As Date
theDate = Date()

Debug.Print theDate

End Sub

The result shown in the Immediate Window is:

VBA Now Function

You can use the Now Function to return the current date and time.

The syntax of the Now Function is Now(). It has no arguments.

The following code shows you how to use the Now Function:

Sub UsingTheNowFunction()

Dim theDate As Date
theDate = Now()

Debug.Print theDate

End Sub

The result is:

VBA Time Function

You can use the Time Function to return the current time.

The syntax of the Time Function is Time(). It has no arguments.

The following code shows you how to use the Time Function:

Sub UsingTheTimeFunction()

Dim theTime As Date
theTime = Time()

Debug.Print theTime

End Sub

The result is:

VBA DateAdd Function

You can use the DateAdd Function to add a date/time interval to a date or time, and the function will return the resulting date/time.

The syntax of the DateAdd Function is:

DateAdd(Interval, Number, Date) where:

• Interval – A string that specifies the type of interval to use. The interval can be one of the following values:

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

• Number – The number of intervals that you want to add to the original date/time.
• Date – The original date/time.

Note: When using dates in your code you have to surround them with # or quotation marks.

The following code shows  how to use the DateAdd Function:

Sub UsingTheDateAddFunction()

Dim laterDate As Date

laterDate = DateAdd("m", 10, "11/12/2019")

Debug.Print laterDate

End Sub

The result is:

VBA DateDiff Function

You can use the DateDiff Function in order to get the difference between two dates, based on a specified time interval.

The syntax of the DateDiff Function is:

DateDiff(Interval, Date1, Date2, [Firstdayofweek], [Firstweekofyear]) where:

• Interval – A string that specifies the type of interval to use. The interval can be one of the following values:

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

• Date1 – A date value representing the earlier date.
• Date2 – A date value representing the later date.
• Firstdayofweek (Optional) – A constant that specifies the weekday that the function should use as the first day of the week. If blank Sunday is used as the first day of the week. Firstdayofweek can be one of the following values:

-vbSunday – uses Sunday as the first day of the week.
-vbMonday – uses Monday as the first day of the week.
-vbTuesday – uses Tuesday as the first day of the week.
-vbWednesday – uses Wednesday as the first day of the week.
-vbThursday – uses Thursday as the first day of the week.
-vbFriday – uses Friday as the first day of the week.
-vbSaturday – uses Saturday as the first day of the week.
-vbUseSystemDayOfTheWeek – uses the first day of the week that is specified by your system’s settings.

• Firstweekofyear (Optional) – A constant that specifies the first week of the year. If blank then the Jan 1st week is used as the first week of the year. Firstweekofyear can be one of the following values:

-vbFirstJan1 – uses the week containing Jan 1st.
-vbFirstFourDays – uses the first week that contains at least four days in the new year.
-vbFirstFullWeek – uses the first full week of the year.
-vbSystem – uses the first week of the year as specified by your system settings.

The following code shows you how to use the DateDiff Function:

Sub UsingTheDateDiffFunction()
 
Dim theDifferenceBetweenTwoDates As Long
 
theDifferenceBetweenTwoDates = DateDiff("q", "11/11/2010", "10/12/2012")
 
Debug.Print theDifferenceBetweenTwoDates
 
End Sub

The result is:

VBA Day Function

You can use the Day Function to return the day of an input date.

The syntax of the Day Function is:

Day(Date_value) where:

• Date_value – The date which you want to extract the day from.

The following code shows you how to use the Day Function:

Sub UsingTheDayFunction()

Dim theDay As Integer

theDay = Day("10/12/2010")

Debug.Print theDay

End Sub

The result is:

VBA Hour Function

You can use the Hour Function to return the hour of an input time.

The syntax of the Hour Function is:

Hour(Time) where:

• Time – The time that you want to extract the hour from.

The following code shows you how to use the Hour Function:

Sub UsingTheHourFunction()
 
Dim theHour As Integer

theHour = Hour("2:14:17 AM")

Debug.Print theHour

End Sub

The result is:

VBA Minute Function

You can use the Minute Function to return the minute value of an input time.

The syntax of the Minute Function is:

Minute(Time) where:

• Time – The time that you want to extract the minute value from.

The following code shows you how to use the Minute Function:

Sub UsingTheMinuteFunction()
 
Dim theMinuteValue As Integer

theMinuteValue = Minute("2:14:17 AM")

Debug.Print theMinuteValue

End Sub

The result is:

VBA Second Function

You can use the Second Function to return the second value of an input time.

The syntax of the Second Function is:

Second(Time) where:

• Time – The time that you want to extract the second value from.

The following code shows you how to use the Second Function:

Sub UsingTheSecondFunction()
 
Dim theSecondValue As Integer

theSecondValue = Second("2:14:17 AM")

Debug.Print theSecondValue

End Sub

The result is:

VBA Month Function

You can use the Month Function to return the month of an input date.

The syntax of the Month Function is:

Month(Date_value) where:

• Date_value – The date which you want to extract the month from.

The following code shows you how to use the Month Function:

Sub UsingTheMonthFunction()
 
Dim theMonth As Integer

theMonth = Month("11/18/2010")
Debug.Print theMonth

End Sub

The result is:

More examples of the VBA Month function:

MsgBox Month(#5/14/2019 5:25:00 AM#)

Result: 5

MsgBox Month("8/14/2019 15:33:00")

Result: 8

MsgBox Month("11/14/2019")

Result: 11

MsgBox Month(Now)

Result will be the month of the current system date.

VBA MonthName Function

You can use the MonthName Function to return the name of a month from an input supplied month number.

The syntax of the MonthName Function is:

MonthName(Number_of_month, [Abbreviate]) where:

• Number_of_month – An integer value between 1 and 12.
• Abbreviate (Optional) – Specifies whether the month name should be abbreviated. If blank the default value of False is used.

Sub UsingTheMonthNameFunction()
 
Dim theMonthName As String

theMonthName = MonthName(12, True)
Debug.Print theMonthName

End Sub

The result is:

More Examples:
MsgBox MonthName(2)

Result: “February”

MsgBox MonthName(2, True)

Result: “Feb”

MsgBox MonthName(9, False)

Result: “September”

VBA TimeSerial Function

The TimeSerial Function takes an input hour, minute and second and returns a time.

The syntax of the TimeSerial Function is:

TimeSerial(Hour,  Minute, Second) where:

• Hour – An integer value between 0 and 23 that represents the hour value.
• Minute – An integer value between 0 and 59 that represents the minute value.
• Second – An integer value between 0 and 59 that represents the second value.

The following code shows you how to use the TimeSerial Function:

Sub UsingTheTimeSerialFunction()

Dim theTime As Date
theTime = TimeSerial(1, 10, 15)

Debug.Print theTime

End Sub

The result is:

VBA TimeValue Function

The TimeValue Function returns a Time from a string representation of a date or time.

The syntax of the TimeValue Function is:

TimeValue(Time) where:

• Time – A String representing the time.

The following code shows you how to use the TimeValue Function:

Sub UsingTheTimeValueFunction()

Dim theTime As Date
theTime = TimeValue("22:10:17")

Debug.Print theTime

End Sub

The result is:

VBA Weekday Function

You can use the Weekday Function to return an integer from 1 – 7 representing a day of the week for a date.

The syntax of the Weekday Function is:

Weekday(Date, [Firstdayofweek]) where:

• Date – The date that you want to extract the weekday value from.
• Firstdayofweek (Optional) – A constant that specifies the weekday that the function should use as the first day of the week. If blank Sunday is used as the first day of the week. Firstdayofweek can be one of the following values:

-vbSunday – uses Sunday as the first day of the week.
-vbMonday – uses Monday as the first day of the week.
-vbTuesday – uses Tuesday as the first day of the week.
-vbWednesday – uses Wednesday as the first day of the week.
-vbThursday – uses Thursday as the first day of the week.
-vbFriday – uses Friday as the first day of the week.
-vbSaturday – uses Saturday as the first day of the week.
-vbUseSystemDayOfTheWeek – uses the first day of the week that is specified by your system’s settings.

The following code shows you how to use the Weekday Function:

Sub UsingTheWeekdayFunction()

Dim theWeekDay As Integer
theWeekDay = Weekday("11/20/2019")
Debug.Print theWeekDay

End Sub

The result is:

Here are some more examples of Weekday:

MsgBox Weekday("1/1/2019", vbMonday)

Result: 2

MsgBox Weekday("1/1/2019", vbTuesday)

Result: 1

MsgBox Weekday("1/1/2019", vbFriday)

Result: 5

VBA WeekdayName Function

You can use the WeekdayName Function to return the name of a weekday from an input supplied weekday number.

The syntax of the WeekdayName Function is:

WeekdayName(Weekday, [Abbreviate], [Firstdayoftheweek]) where:

• Weekday – An integer value between 1 and 7.
• Abbreviate (Optional) -Specifies whether the weekday name should be abbreviated. If blank the default value of False is used.
• Firstdayofweek (Optional) – A constant that specifies the weekday that the function should use as the first day of the week. If blank Sunday is used as the first day of the week. Firstdayofweek can be one of the following values:

-vbSunday – uses Sunday as the first day of the week.
-vbMonday – uses Monday as the first day of the week.
-vbTuesday – uses Tuesday as the first day of the week.
-vbWednesday – uses Wednesday as the first day of the week.
-vbThursday – uses Thursday as the first day of the week.
-vbFriday – uses Friday as the first day of the week.
-vbSaturday – uses Saturday as the first day of the week.
-vbUseSystemDayOfTheWeek – uses the first day of the week that is specified by your system’s settings.

Sub UsingTheWeekdayNameFunction()
 
Dim theWeekdayName As String

theWeekdayName = WeekdayName(4)
Debug.Print theWeekdayName

End Sub

The result is:

VBA Year Function

You can use the Year Function to return the year of an input date.

The syntax of the Year Function is:

Year(Date_value) where:

• Date_value – The date which you want to extract the year from.

The following code shows you how to use the Year Function:

Sub UsingTheYearFunction()

Dim theYear As Integer

theYear = Year("11/12/2010")
Debug.Print theYear

End Sub

The result is:

More Examples:

MsgBox Year("1/1/1999")

Result: 1999

MsgBox Year(#1/1/2001 1:30:00 AM#)

Result: 2001

MsgBox Year("1/1/05")

Result: 1905

MsgBox Year(Date)

Result will be the year of the current system date.

Sub Year_Example()
    MsgBox Year("1/1/2019")
End Sub

This code will return 2019.

Comparing Dates in VBA

You can compare dates using the >, <, and = operators in VBA. The following code shows you how to compare two dates in VBA.

Sub ComparingDates()

Dim dateOne As Date
Dim dateTwo As Date

dateOne = "10/10/2010"
dateTwo = "11/11/2010"

If dateOne > dateTwo Then
Debug.Print "dateOne is the later date"

ElseIf dateOne = dateTwo Then
Debug.Print "The two dates are equal"

Else
Debug.Print "dateTwo is the later date"

End If
End Sub

Learn more about how to Format dates as strings by viewing this tutorial.

IsDate Function

IsDate will test if an expression is a valid date and return TRUE or FALSE.

Examples:

Sub IsDate_Example()
    MsgBox IsDate("4/12/2019")
End Sub

This will return True.

MsgBox IsDate("4\12\2019")

This will return false because the slashes are backwards.

More Valid Dates:

MsgBox IsDate("8/22/2019")
MsgBox IsDate("8 22 19")
MsgBox IsDate("Aug 22 19")
MsgBox IsDate("8,22,2019")
MsgBox IsDate("8-22-19")
MsgBox IsDate("8/22")
MsgBox IsDate("8-22")

Result: True

Invalid Dates:

MsgBox IsDate("8.22.2019")
MsgBox IsDate("8\22\2019")
MsgBox IsDate("Aug")
MsgBox IsDate("2019")

Result: False