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.
Search JabSto ::




Custom Search