Date and Time Functions
A number of functions deal specifically with date and time. 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 the Windows Control Panel:
MsgBox Date
Time
The Time function returns the current system time:
MsgBox Time
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 prompt 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. 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 truncates 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 per the format in the Windows Control Panel rather than as an actual number.