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.).
| 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. |
Related Office Online discussions
Read related questions and answers from other Microsoft Office customers.
United Kingdom
Printer-Friendly Version
Show All
Hide All
