*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**

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

*Fig. 3*

*Fig. 4*

**DateDiff Example 2: Calculate Age in Years Based on Current Date**

Fig. 5

Fig. 5

Fig. 6Fig. 6

DateDiff Example 3 – Calculate Age in Days and Years Based on Date Other than Today

DateDiff Example 3 – Calculate Age in Days and Years Based on Date Other than Today

*Fig. 7*

*Fig. 8*

Fig. 9Fig. 9

Fig. 10Fig. 10

**Conclusion**