Date functions overview

This page discusses three ways to fill a date variable:

Additionally, it links to pages that cover all kind of date related calculations:

Day & weekMonthQuarterYear
Day of the week - 1 = sunday First day in month First day in quarter Year - from date
Name of WeekDay or abbreviations Last day in month Last day in quarter Is leap year
Today, YesterdayTomorrow Day in month First month in quarter Number of day in year
Next and previous day Month - Number Last month in quarter First day in year
  Name of month   Last day in year

Setting date values

Use Date constant syntax

You can use fixed dates in your VBA code by putting the date between # #, as below.

Const cdtMyDate As Date = #12/31/2001#

Create date from year, month and day - DateSerial

The DateSerial function fills a date variable from year, month and day - see code below.

Dim iYear As Integer: iYear =
Dim iMonth As Integer: iMonth =
Dim iDay As Integer: iDay =
Dim dt As Date
dt = DateSerial(Year:=iYear, Month:=iMonth, Day:=iDay)

Convert string to date - DateValue

The DateValue function converts a string to a date. The code below interprets the string as 2/12/2009.

Dim strDate As String: strDate = "February 12, 2009"
Dim dt As Date
dt = DateValue(strDate)
Note
  • DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form. For example, in addition to recognizing 12/30/1991 and 12/30/91, DateValue also recognizes December 30, 1991 and Dec 30, 1991.
  • If the year part of date is omitted, DateValue uses the current year from your computer's system date.
  • If DateValue can't resolve the string to a date Run-time error '13': Type mismatch occurs