MS-Excel / Functions and Formula

Excel VBA's Date and Time Functions

VBA provides a full complement of date and time functions, as listed in Table below. The table provides brief examples of working with the functions. The sections after the table provide longer examples of working with some of the more complex functions.

VBA's date and time functions
Function(Arguments)ReturnsExample
DateA Variant/Date containing the current date according to your computerMsgBox Date might display 04/01/2010. (The format depends on your Windows date settings.)
DateAdd(interval, number, date)A Variant/Date containing the date of the specified interval after the specified dateDateAdd("m", 1, "6/3/06") returns 7/3/2010.
DatePart(interval, date)The part (specified by interval) of the specified dateSee the example in the next section.
DateSerial(year, month, day)A Variant/Date containing the date for the specified year, month, and daydteCompanyFounded = DateSerial(1997, 7, 4).
DateValue(date)A Variant/Date containing the specified datedteDeath = "July 2, 1971"
Day(date)A Variant/Integer between 1 and 31, inclusive, representing the day of the month for dateIf Day(Date) = 1 And Month(Date) = 1 Then MsgBox "Happy new year!"
Hour(time)A Variant/Integer between 0 and 23, inclusive, representing the hour for timedteHour = Hour(dteLoggedIn)
Minute(time)A Variant/Integer between 0 and 59, inclusive, representing the minute for timedteMinute = Minute(dteLoggedIn)
Month(date)A Variant/Integer between 1 and 12, inclusive, representing the month for datestrThisDate = Month(Date) & "/" & Day(Date)
MonthName(month)A String containing the name of the month represented by monthMsgBox MonthName(Month(Date)) displays a message box containing the current month.
NowA Variant/Date containing the current date and time according to your computerMsgBox Now might display 04/01/2010 9:25:15PM. (The format of date and time will depend on your Windows date settings.)
Second(time)A Variant/Integer between 0 and 59, inclusive, representing the second for timedteSecond = Second(dteLoggedIn)
TimeA Variant/Date containing the current time according to your computerMsgBox Time might display 9:25:15PM. (The time format and time will depend on your Windows date settings.)
Second(time)A Variant/Integer between 0 and 59, inclusive, representing the second for timedteSecond = Second(dteLoggedIn)
TimeA Variant/Date containing the current time according to your computerMsgBox Time might display 9:25:15PM. (The time format and time will depend on your Windows date settings.)
TimerA Single giving the number of seconds that have elapsed since midnightIf Timer > 43200 Then MsgBox _ "This code only works in the morning.": End
TimeSerial(hour, minute, second)A Variant/Date containing the time for the specified hour, minute, and secondTimeSerial(11, 12, 13) returns 11:12:13AM. (The format will depend on your Windows date settings.)
TimeValue(time)A Variant/Date containing the time for timeTimeValue(Now)
Weekday(date)A Variant/Integer containing the day of the week represented by dateSee the next entry.
WeekdayName (weekday)A String containing the weekday denoted by weekdayWeekdayName(Weekday (#4/1/2010#)) returns Saturday, the day of the week for April Fool's Day 2010.

Using the DatePart Function to Parse Dates

The DatePart function lets you take a date and separate it into its components. You can often achieve the same results by using other date functions, but DatePart is a great tool to have in your VBA toolbox.

The syntax for DatePart is as follows:

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

The components of the syntax are as follows:

  • interval is a required String expression giving the unit in which you want to measure the interval: yyyy for year, q for quarter, m for month, y for the day of the year, d for day, w for weekday, ww for week, h for hour, n for minute (because m is for month), and s for second.
  • date is a required Variant/Date giving the date you want to examine.
  • firstdayofweek is an optional constant specifying the day that starts the week (for date information). The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; this uses the system setting).
  • firstweekofyear is an optional constant specifying the week considered first in the year.

Table below shows the options for this constant.

The options for the Firstweekofyear constant
Constant 	Value 	Year Starts with Week
vbUseSystem 	0 	Use the system setting.
vbFirstJan1 	1 	The week in which January 1 falls (the
			default setting).
vbFirstFourDays 2 	The first week with a minimum of four
			days in the year.
vbFirstFullWeek 3 	The first full week (7 days) of the year.

For example, the following statement assigns the current year to the variable dteThisYear:

dteThisYear = DatePart("yyyy", Date)
[Previous] [Contents] [Next]