Can I use the
DateDiff function to count intervals that aren't contiguous? Specifically, I need to compute the number of working days between two dates, taking into account weekends and holidays.
With a little creativity, you can easily compute the number of days between two date/time values, skipping any weekends and holidays that fall between them. First, you need to create a table containing the holidays you wish to subtract (like the one shown in Figure 8-4). Next, you'll need to create a similar table containing weekend dates.
Figure 8-4. A list of holidays
You can then compute the number of working days between any two dates by finding the total number of days between the dates and subtracting the number of holidays and weekend days. If you use this SQL statement, your results should look like the datasheet view shown in Figure 8-5:
SELECT [Chapter8-2A].TaskId, [Chapter8-2A].StartDate, [Chapter8-2A].StopDate, DateDife("d",[StartDate],[StopDate]) AS Days, DateDife("d",[StartDate],[StopDate]) -(Select count(*) From Holidays Where Holiday Between StartDate And StopDate) AS NoHolidays, DateDife("d",[StartDate],[StopDate])
Figure 8-5. Counting days, excluding weekends and holidays
-(Select count(*) From Weekends Where Weekend Between ...