Microsoft Office Tutorials and References

In Depth Information

**Performing Date and Time Calculations**

Performing Date and Time Calculations

The built-in Date and Time functions operate on data stored in Date/Time

fields. You can perform some basic calculations — called
date arithmetic

calculations
— on dates by using simple + (addition) and – (subtraction)

operators. Date arithmetic calculations follow a few simple rules:

✦
If you subtract one date from another, you get a number indicating

the number of days between those dates.
1/15/2013–1/1/2013

returns 14 because there are 14 days between January 15 and January 1.

✦
If you add a number to, or subtract a number from, a date, you get a

new date rather than a number.
That new date is the date that’s
n days

away from the original date (where
n stands for the number of days you

add or subtract). 1/1/2013+30 returns 1/31/2013 because January 31

is 30 days after January 1. The result of 12/31/2000–999 is 4/7/1998

because April 7, 1998, is 999 days before 12/31/2000.

Figure 2-9 shows a sample query that uses some basic arithmetic in query

calculated fields. In the underlying table, both the StartDate and EndDate

fields are defined as the Date/Time data type (with the Short Date format).

Figure 2-9:

Sample

calculated

fields with

Date

functions

in a query

in Design

view and

Datasheet

view.

The first calculated field is the following expression:

DaysBetween: [EndDate]-[StartDate]

This field calculates and displays the number of days between the

StartDate value and the EndDate value in each record.