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)	Returns	Example
Date	A Variant/Date containing the current date according to your computer	MsgBox 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 date	DateAdd("m", 1, "6/3/06") returns 7/3/2010.
DatePart(interval, date)	The part (specified by interval) of the specified date	See the example in the next section.
DateSerial(year, month, day)	A Variant/Date containing the date for the specified year, month, and day	dteCompanyFounded = DateSerial(1997, 7, 4).
DateValue(date)	A Variant/Date containing the specified date	dteDeath = "July 2, 1971"
Day(date)	A Variant/Integer between 1 and 31, inclusive, representing the day of the month for date	If 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 time	dteHour = Hour(dteLoggedIn)
Minute(time)	A Variant/Integer between 0 and 59, inclusive, representing the minute for time	dteMinute = Minute(dteLoggedIn)
Month(date)	A Variant/Integer between 1 and 12, inclusive, representing the month for date	strThisDate = Month(Date) & "/" & Day(Date)
MonthName(month)	A String containing the name of the month represented by month	MsgBox MonthName(Month(Date)) displays a message box containing the current month.
Now	A Variant/Date containing the current date and time according to your computer	MsgBox 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 time	dteSecond = Second(dteLoggedIn)
Time	A Variant/Date containing the current time according to your computer	MsgBox 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 time	dteSecond = Second(dteLoggedIn)
Time	A Variant/Date containing the current time according to your computer	MsgBox Time might display 9:25:15PM. (The time format and time will depend on your Windows date settings.)
Timer	A Single giving the number of seconds that have elapsed since midnight	If 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 second	TimeSerial(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 time	TimeValue(Now)
Weekday(date)	A Variant/Integer containing the day of the week represented by date	See the next entry.
WeekdayName (weekday)	A String containing the weekday denoted by weekday	WeekdayName(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)