Date Diff Function

Named Arguments

Syntax

DateDiff interval, datel, date2 [, firstdayofweek [, firstweekofyear]])

interval

Use: Required

Data Type: String

The units of time used to express the result of the difference between datel and date2 (see the table "Interval Settings").

datel

Use: Required

Data Type: Variant (Date)

The first date you want to use in the differential calculation.

date2

Use: Required

Data Type: Variant (Date)

The second date you want to use in the differential calculation.

firstdayofweek Use: Optional

Data Type: Numeric constant

A numeric constant that defines the first day of the week. If not specified, Sunday is assumed (see the table "First Day of Week Constants").

firstweekofyear Use: Optional

Data Type: Numeric constant

A numeric constant that defines the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs (see the table "First Week of Year Constants").

198 Chapter 7- The Language Reference

Interval Settings

Setting

Description

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Second

First Day of Week Constants

Constant

Value

Description

vbUseSystem

0

Use the NLS API setting

vbSunday

1

Sunday (default)

vbMonday

2

Monday

vbTuesday

3

Tuesday

vbWednesday

4

Wednesday

vbThursday

5

Thursday

vbFriday

6

Friday

vbSaturday

7

Saturday

First Week of Year Constants

Constant

Value

Description

vbUseSystem

0

Use the NLS API setting.

vbFirstJanl

1

Start with the week in which January 1 occurs (default).

vbFirstFourDays

2

Start with the first week that has at least four days in the new year.

vbFirstFullWeek

3

Start with first full week of the year.

Return Value

Variant (Long). Description

Returns a variant of subtype long specifying the number of time intervals between two specified dates.

DateDiff Function 199

The DateDiff function calculates the number of time intervals between two dates.

For example, you can use the function to determine how many days there are between 1 January 1980 and 31 May 1998.

Rules at a Glance

• The calculation performed by DateDiff is always date2—date1. Therefore, if datel chronologically precedes date2, the value returned by the function is negative.

• If interval is Weekday "w", DateDiff returns the number of weeks between date1 and date2. DateDifftotals the occurrences of the day on which date1 falls, up to and including date2, but not including date1. Note that an interval of "w" doesn't return the number of weekdays between two dates, as you might expect.

• If interval is Week "tow", DateDiff returns the number of calendar weeks between date1 and date2. To achieve this, DateDiff counts the number of Sundays (or whichever other day is defined to be the first day of the week by the firstdayofweek argument) between date1 and date2. If date2 falls on a Sunday, it's counted, but date1 isn't counted even if it falls on a Sunday.

• The firstdayofweek argument affects only calculations that use the "ww" (week) interval values.

Example

Dim dtNow As Date Dim dtThen As Date Dim sInterval As String Dim lNoOfIntervals As Long dtNow = Date dtThen = "01/01/1990" sInterval = "m"

lNoOfIntervals = DateDiff(sInterval, dtThen, dtNow) MsgBox lNoOfIntervals

Programming Tips & Gotchas

• When working with dates, always check that a date is valid using the IsDate function prior to passing it as a function parameter.

• When comparing the number of years between December 31 of one year to January 1 of the following year, DateDiff returns 1 although in reality, the difference is only one day.

• DateDiff considers the four quarters of the year to be January 1-March 31, April 1-June 30, July 1-September 30, and October 1-December 31. Consequently, when determining the number of quarters between March 31 and April 1 of the same year, for example, DateDiffreturns 1, even though the latter date is only one day after the former.

• If interval is "m", DateDiff simply counts the difference in the months on which the respective dates fall. For example, when determining the number

200 Chapter 7- The Language Reference of months between January 31 and February 1 of the same year, DateDiff returns 1, even though the latter date is only one day after the former.

• To calculate the number of days between datel and date2, you can use either Day of year "y" or Day "d".

• In calculating the number of hours, minutes, or seconds between two dates, if an explicit time isn't specified, DateDiff provides a default value of midnight (00:00:00).

• If you specify datel or date2 as strings within quotation marks (" ") and omit the year, the year is assumed to be the current year, as taken from the computer's date. This allows the same code to be used in different years.

See Also

DateAdd Function, DatePart Function, IsDate Function

Was this article helpful?

0 0

Post a comment