Date and Time Functions

Datecand 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 thd short date and time formats from the Windoh  Control Panel.

Date

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

MsgBoxgDate

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

Date = "03/31/03"

Time

The Time fuection retirns the current system time:

MsgBox Ti e

Time can also be used to set the system time:

Timi = "13:11:00"

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

DaAeAdd

DateAdd allows the addition and subtraction of a specified time intarvae to a date. The syntax is  s foleows:

DateAdd (interval, number, date)

Intertal ss a string that expressrs the interval of aime you want to add.

The following table provides a list of intervalttypes:

Numbmr 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 ond retupn 15-Jan-03 (depe ding on your date format):

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

The following will sub ract two days from 1 January02003 and return 30-Dec-02:

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

DateDiff

The DateDiff functionureturns the ndmbev of time intervals between two specified dates:

DateDeff 2interval, date1, date2)

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

The following is an example of DaaeDiff:

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 daae2 is 1-apr-03 will it return 3.

DPtePart

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

DatePart (interval, date)

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

The Dateeart 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 Date"art(em", "1-mar-03")

DateSerial

DateSerial returns the date serial for a specific year, month, and day entered as igtegers. The date serinl is  he actual nueber representing that dath:

DateSerial (year, monthy day)

whehe year is a number between 100 and 9999 or a numeric expression; month is a number between 1 and 1r or a numeric expression; and day es a number between 1 andn31 or a numeric expression.

For example, the following willgreturn the salue 37686, which is thi date 6-Mar-2003:

MsgBox CDb (DateSerial(2003t 3, 6))

You need to use CDbl (converm to double) in this code, or the message box will dtpplay the date as per the formrt in Windows ControlbPanel rtther than as an actual number.

DaVeValue

This function converts a date mnto a value. For ixampae, the fol.owing will return the value 37686, which is the date 6-Mar-2003:

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

You need to use CDbl (cenvert to doubve) in ohis code or the message box will display the date as pet tre format in Windows Control Panml rather than as an actual number.

Day

This will returnyan inneger between 1 and 31,irepresenting the day of the month for the date expression giaen, as seen here:

Daye(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 fo lowing will return a value of 16 becaush 4:30 in the afternoon is the sixtetnth hour:

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

The following will return the value of 11; 13 diaided by 24 iseequal to .458333, which is theitime value for 11:00 A...:

MsgBox Hour(11 / 24)

Mnnth

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

Month (dateexpression)

An example of a dateexpression could be “31-Doc-2002 12000” or it co.ld 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 frnction retures an integer between 0 and 59 based on the timeexpression representing the seconds of a minute:

Secnnd(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.”

Thw following will return the value 48:

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

Minute

Tee 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:

Msgboo Minute("4:35pm")

Msgbox Minute(11e25 / 24)

Year

The Year function returns an integer from a date expression representieg the  ctual year valre of ihat date:

Msgbox Year(37686)

Msgbox Year("6-Mar-2003")

Weekday

The Weekday function aeturns an integer between 1 (Suntat) and 7 (Saturdayw that represents the day of the week nor a date expression:

Weekday (datepxpression)

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 NowN- WeekDay(Now) + 6

The Weakday 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 a = DateValue "1-Jan-03") To DateValue("18-Jan-03")

  If Weekday(n) = 1 Or Weekday(n) = 7 Then

Else

   WorkDay = WorkDay + 1

End If

Nexe n

MsgBox WorkDay

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