Date and Time Functions

There are a number of functions dealing specifically with date and time, and these are included in this section.

Now

The Now function returns the current date and time:

 MsgBox Now 

This displays the short date and time formats from the Windows Control Panel.

Date

Date returns the current date in short format as defined in Windows Control Panel:

 MsgBox Date 

Date can also be used to set the current system date:

 Date = "03/31/03" 

Time

The Time function returns the current system time:

 MsgBox Time 

Time can also be used to set the system time:

 Time = "13:11:00" 

The preceding line is an example of setting the time to 11 minutes past one in the afternoon.

DateAdd

DateAdd allows the addition and subtraction of a specified time interval to a date. The syntax is as follows :

 DateAdd (interval, number, date) 

Interval is a string that expresses the interval of time you want to add.

The following table provides a list of interval types:

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

Number is a numeric that determines the number of intervals you want to add. A negative value is allowed and will cause subtraction from the date.

Date is the date being added to or the name of a variant containing the date. This example will add one month to January and return 1-Feb-03:

 MsgBox DateAdd ("m",1,"1-Jan-03") 

The following will add two weeks and return 15-Jan-03 (depending on your date format):

 MsgBox DateAdd ("ww",2,"1-Jan-03") 

The following will subtract two days from 1 January 2003 and return 30-Dec-02:

 MsgBox DateAdd ("d", -2, "1-Jan-03") 

DateDiff

The DateDiff function returns the number of time intervals between two specified dates:

 DateDiff (interval, date1, date2) 

Interval is a string expression based on the following table to show the type of interval, and the date1 string indicates the start date and date2 the end date.

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

The following is an example of DateDiff :

 MsgBox DateDiff("m", "1-jan-03", "15-mar-03") 

This will return the result 2 because there are two months between 1-jan-03 and 15-mar-03. Note that it rounds to the lower month. If date2 was 30-mar-03, it would still return 2. Only when date2 is 1-apr-03 will it return 3.

DatePart

The DatePart function returns a specified part of a given date:

 DatePart (interval, date) 

Interval is the time period based on the following table, and date is the date you want to inspect.

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

The DatePart syntax is as follows:

 MsgBox DatePart("q", "1-mar-03") 

This will return the result 1 because 1-Mar-03 is in quarter 1.

The following will return the result 3 because March is the third month:

 MsgBox DatePart("m", "1-mar-03") 

DateSerial

DateSerial returns the date serial for a specific year, month, and day entered as integers. The date serial is the actual number representing that date:

 DateSerial (year, month, day) 

where year is a number between 100 and 9999 or a numeric expression; month is a number between 1 and 12 or a numeric expression; and day is a number between 1 and 31 or a numeric expression.

For example, the following will return the value 37686, which is the date 6-Mar-2003:

 MsgBox CDbl(DateSerial(2003, 3, 6)) 

You need to use CDbl (convert to double) in this code, or the message box will display the date as per the format in Windows Control Panel rather than as an actual number.

DateValue

This function converts a date into a value. For example, the following will return the value 37686, which is the date 6-Mar-2003:

 Msgbox CDbl(DateValue("06-Mar-2003")) 

You need to use CDbl (convert to double) in this code or the message box will display the date as per the format in Windows Control Panel rather than as an actual number.

Day

This will return an integer between 1 and 31, representing the day of the month for the date expression given, as seen here:

 Day (dateexpression) 

Dateexpression can be a date string or it can be a numeric expression representing a date.

Both of the following return the value 6 for the sixth day of March because they represent the same date:

 Msgbox Day(37686) 
 Msgbox Day("6-Mar-2003") 

Hour

Hour returns an integer between 0 and 23 representing the hour of the day for the date expression:

 Hour(dateexpression) 

An example of a dateexpression could be ‚“31-Dec-2002 12:00 ‚½ or it could be a time without the date, such as ‚“09:00 ‚½:

 MsgBox Hour("17:50") 

This will return a value of 17 for the seventeenth hour.

The following will return a value of 16 because 4:30 in the afternoon is the sixteenth hour:

 MsgBox Hour("6-Mar-2003 4:30pm") 

The following will return the value of 11; 11 divided by 24 is equal to .458333, which is the time value for 11:00 a.m.:

 MsgBox Hour(11 / 24) 

Month

Month returns an integer between 1 and 12, based on the date expression:

 Month (dateexpression) 

An example of a dateexpression could be ‚“31-Dec-2002 12:00 ‚½ or it could be a time without the date, such as ‚“09:00. ‚½

The following will both return the value of 3 because both date expressions represent 6-Mar-2003:

 Msgbox Month(37686) 
 Msgbox Month("6-Mar-2003") 

Second

The Second function returns an integer between 0 and 59 based on the timeexpression representing the seconds of a minute:

 Second(timeexpression) 

An example of a timeexpression could be ‚“31-Dec-2002 12:00 ‚½ or it could be a time without the date such as ‚“09:00. ‚½

The following will return the value 48:

 Msgbox Second("4:35:48pm") 

Minute

The Minute function returns an integer from a time expression representing the actual minute of that time.

 Msgbox Minute(11.27 / 24) 

This will return the value 16, since 11.27 is 11:16:12 a.m. This may look confusing because we are dealing with decimal parts of an hour. The expression 11.27 is a decimal of an hour, so .27 is just over a quarter of an hour.

Following are two examples of the Minute function:

 Msgbox Minute("4:35pm") 
 Msgbox Minute(11.25 / 24) 

Year

The Year function returns an integer from a date expression representing the actual year value of that date:

 Msgbox Year(37686) 
 Msgbox Year("6-Mar-2003") 

Weekday

The Weekday function returns an integer between 1 (Sunday) and 7 (Saturday) that represents the day of the week for a date expression:

 Weekday (dateexpression) 
 MsgBox WeekDay("6-Mar-2003") 

This will return the value 5, which is Thursday.

This function can be useful if you want a date to always default to a particular day of the week. For example, if you always want a date to show the week ending Friday for the current week, you could use the following formula:

 MsgBox Now - WeekDay(Now) + 6 

The Weekday function starts from Sunday, so it reduces Now back to the last Sunday and then adds 6 to get to Friday. You can also use it to calculate the number of working days between two dates:

 For n = DateValue("1-Jan-03") To DateValue("18-Jan-03") 
 
 If Weekday(n) = 1 Or Weekday(n) = 7 Then 
 
 Else 
 
 WorkDay = WorkDay + 1 
 
 End If Next n 
 
 MsgBox WorkDay 

WorkDay will return the value of 13, which is the number of working days between the two dates.