DateDiff( )

The DateDiff() function returns the difference between two dates or times. It can return the difference in different intervals. These intervals can be days, months, years or any of the intervals listed in Table 9-11.

Be careful when using the DateDiff() function for calculating an interval other than the number of days. When performing a difference calculation, it counts any interval less than a single unit as zero. For example, if you want to find out how many months have elapsed between two dates, and the two dates are the first day of the month and the last day of the month, the result is 0. This is because the interval is only a partial month and doesn’t constitute a full month. The fact that the dates are 30 days apart is irrelevant. If you change this example so that rather than use the last day of the month, you use the first day of the next month, the result is 1. Even though the two examples had final dates that only differed by one day, the result is different. This applies to all the intervals including dates and times.

There is an optional parameter that lets you specify the first day of the week. This is only used by the DateDiff() function when the interval is ww. This counts the number of times a particular day of the week appears within a date range. To pass this parameter to the function, prefix the day by cr. For example, Friday is crFriday. The start date does not get counted when doing the calculation, but the end date does. Thus, if you pass the function a start date that falls on a Friday, and the parameter is crFriday, the result will not include this date.

‘Demonstrate counting the number of paydays
Dim StartDate as Date
Dim NumberOfFridays As Number
StartDate = DateSerial(Year(CurrentDate), 1,1) ‘First day of year
NumberOfFridays = DateDiff("ww", #1/1/2002#, CurrentDate, crFriday)
‘If the first date was a Friday, add it back
If WeekDay(StartDate) = 6 Then
NumberOfFridays = NumberOfFridays + 1
End If
Formula = NumberOfFridays \ 2 ‘Paid on every other Friday

The DateDiff() function treats the w and ww intervals differently than the DateAdd() and DatePart() functions. In both the DateAdd() and DatePart() functions, the w interval represents a single weekday and the ww interval represents a seven day period. However, the DateDiff() function treats the w interval as the number of weeks between two dates and the ww interval counts the number of times a certain weekday appears. Thus, the ww interval counts the number of times a seven-day period occurs and the w interval counts the number of times a single day occurs. This is the exact opposite of how the other two functions treat these intervals.