With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

Counting Elapsed Time with Exceptions

Problem

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.

Solution

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.

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])```
` -(Select count(*) From Weekends Where Weekend Between ...`

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required