O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

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

Start Free Trial

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.

A list of holidays

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])
Counting days, excluding weekends and holidays

Figure 8-5. Counting days, excluding weekends and holidays

 -(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.

Start Free Trial

No credit card required