Microsoft Office Tutorials and References

In Depth Information

DateDiff ("
interval
",
date1
,
date2
,
firstdayofweek
,
firstweekofyear
)

The arguments of the
DateDiff
function are listed in Table 9-7.

Table 9-7.
The
DateDiff
Function’s Arguments

Argument

Description

Interval

An interval value listed in Table 9-6.

Date1

The first date to use in the calculation.

Date2

The second date to use in the calculation.

firstdayofweek

A constant that specifies the first day of the week. If not specified,

Sunday is assumed. The available constants are
vbUseSystem
(use

the system setting),
vbSunday
,
vbMonday
,
vbTuesday
,
vbWednesday
,

vbThursday
,
vbFriday
, and
vbSaturday
.

firstweekofyear

A constant that specifies the first week of the year. The available

constants are
vbUseSystem
(use the system setting),
vbFirstJan1
(the

default, which uses the week that contains January 1),
vbFirstFourDays

(uses the first week with at least four days in the new year),

vbFirstFullWeek
(uses the first seven-day week in the new year).

For example, the following function returns the number of days between the current date

and August 2, 2005 (remember that the date needs to be enclosed by pound signs):

DateDiff("d", NOW, #8/2/2005#)

When you subtract times, you don’t need to use a special operator—the subtraction operator

works, but you can run into problems if you cross midnight. For example, if you work from

9:00 P.M. to 6:00 A.M. you have worked nine hours, but subtracting .875 (the time serial of

9:00 P.M.) from .25 (the time serial of 6:00 A.M.) results in a negative number, which you

can’t have when working with time serials. The trick is to add a day to the smaller value to

make the subtraction work. Adding 1 to the time serial for 6:00 A.M. results in the equation

1.25

.875, which equals .375 (nine hours, or 9:00 A.M.). Here’s how you implement this

check in VBA:

'datTime1 is the start time, datTime2 is the finishing time

If datTime1 > datTime2 Then

datTime2 = datTime2 + 1

End If

datDifference = datTime2 – datTime 1