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
An interval value listed in Table 9-6.
The first date to use in the calculation.
The second date to use in the calculation.
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 .
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
.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
datDifference = datTime2 – datTime 1