Using the DateDiff Funtion in Access

Shawn LaRoche, Senior Research Analyst, Mount Wachusett Community College

Purpose of the DateDiff Function
Many ways exist to determine the difference between two points in time in various software packages. In IR, we most commonly use a difference in time function to determine age. This can be accomplished in Microsoft Excel and many offices are likely to have a script or program to determine age. But there may be occasions where determining age, or some other measure of the difference in two points in time, in a repeatable way is needed. Microsoft Access can be a useful tool in this regard by utilizing a fairly simple function called “DateDiff.”

How to Use the DateDiff Function
The DateDiff function measures the difference between two points in time in a variety of intervals, including years, months, and days. Using age as an example, we want to know the number of years between the person’s date of birth and another date. We can use the following structure for the three-parameter DataDiff function to build a query:

DateDiff(interval, date1, date2)

Here are a few details about each parameter:

Interval – A string expression that is used to calculate the difference between date1 and date2. The list of available settings and a description of each can be seen in Figure 1.

Fig. 1

Please note – using yyyy will round up so when trying to determine the number of years between two points in time, an optimal approach is to calculate the number of days and divide by 365.25. The ‘.25’ accounts for leap years.
date1 – The earlier of the two dates used in the function
date2 – The later of the two dates used in the function

DateDiff Example 1 – Calculate Age in Days Based on Current Date
In Figure 2 below, we can see a table with sample students and their birthdays (random dates). Please note the date format must appear as shown in Figure 2.
Fig. 2

Note – For those unfamiliar with Access, tables and queries are found in the ‘All Access Objects’ navigation pane on the left hand side of the screen. Figure 2 above shows the navigation pane while the remaining images exclude it.
To find the student ages as of today, create a query following the logic described earlier: DateDiff(interval, date1, date2). Figure 3 below (saved as a query named ‘AgeDays Query’) includes an example of how to calculate the number of days between the birthdates and today by creating a new field we are calling “Age Days.” Notice date1 is represented as the student birthdate included in the previously referenced table. Date2 is represented by the expression “Now()” (Microsoft Access recognizes “Now()” as the current day and time).
Fig. 3

The results can be seen in Figure 4. The data was compiled on 6/1/2016 so the number of days between two dates is based on that date.

Fig. 4 

DateDiff Example 2: Calculate Age in Years Based on Current Date
Now that the number of days between the birthdate and today has been established, create a second query to calculate the number of years. See Figure 5 below for the query setup and Figure 6 for the results. The second query is saved as “AgeYears Query.” Please note: using the INT (integer) function will not round up and removes decimals places.

Fig. 5

Fig. 6

DateDiff Example 3 – Calculate Age in Days and Years Based on Date Other than Today
What if a user wants to determine how old a student was at a point in time other than today? For example, how old were the students at the beginning of a specific fall semester? A simple change to the date2 parameter can answer this question. The example seen in Figure 7 uses Fall 2014 and assumes September 1, 2014 as the semester start date. Figure 7 shows the adjustment to the query and the appropriate formatting (MM/DD/YYYY) while Figure 8 shows the results.

Fig. 7

Fig. 8
Similar to the previous example, create a second query to convert the number of days into years (see Figures 9 and 10). Notice in Figure 10 that Student A and Student B are the same age despite being born in different years. This happens because the age is based on how old they were in terms of years as of September 1, 2014.

Fig. 9


Fig. 10

This is also a useful process when trying to determine the difference in two points in time, such as time to degree completion, time employed, etc. Microsoft Access is a powerful tool for IR practitioners, especially when a repeatable metric is needed. This function has helped me in a number of circumstances and I hope it proves useful for you.



To add a comment, Sign In
There are no comments.