Calculate the difference between two date or time values

The following table lists examples of expressions that use the DateDiff function to calculate the difference between any two date and time values.

You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).

 Tip   Try Office 2010 In Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.
Watch a video or try Office 2010.

Expression Description
=DateDiff("d", [OrderDate], [ShippedDate]) Displays the variance in days between the values of the OrderDate and ShippedDate fields.
=DateDiff("yyyy", [Birthday1], [Birthday2]) Displays the variance in years between the values of the Birthday1 and Birthday2 fields.
=DateDiff("m", #12/24/2000#, #11/26/2000#) Displays the variance in months between the two dates. The expression evaluates to -1, since the first date falls after the second date.
=DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays the variance in years between the two dates. The expression evaluates to 1, even though only a day has elapsed.

You can use these expressions in a calculated field in a query.

Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime field the number of days between the values of the OrderDate and ShippedDate fields.
YearsLapsed: DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays in the YearsLapsed field the number of years between the two dates.
 
 
Applies to:
Access 2003