Access Data Analysis Cookbook

Chapter 8. Date and Time Calculations

Nearly every database that has ever existed has contained some sort of date and time data. Yet date and time data is often the most challenging type of data to analyze. While you can analyze date/time values mathematically, the results you get may not be what you expect. Sometimes, you need to do a little programming to get the information you want from your date/time data.

In this chapter, you’ll learn how to correctly perform mathematical operations using date/time values—for example, you’ll see how to compute the time elapsed between two date/time values, and how to compensate for holidays and weekends. You’ll also learn how to work with date/time values recorded in different time zones, and how to extract the components of a date/time value (such as the month, day, and year).

Counting Elapsed Time

Problem

I record the start and end times for tasks I perform at work, and I need to compute how much time I’ve spent on each task. How can I do this without getting out my calculator?

Solution

Access allows you to include VBA functions in calculated fields. The DateDiff function provides an easy way to subtract one date/time value from another and return the difference as a number measured in the units of your choice.

Suppose you have a table containing task identifiers along with start times and stop times for each task (see Figure 8-1), and you want to compute the number of minutes between the two date/time values.

As Figure 8-2 illustrates, you can ...