Date Arithmetic in Queries

I answer a lot of questions regarding date arithmetic and see a lot of over-complicated answers. This may in part be due to the poor MS help documentation. This is a summary of the main answers, from which you should be able to derive most other solutions.

All examples are given as a field definition in the query grid, but other usage (criteria, update to) should be obvious!

What is a date?

Access stores date/time variables like floating point numbers where the integer part represents days since 30/12/1899 and the fractional part represents time of day.

You can use this fact to do arithmetic without any functions in many cases. You need functions if your aritmetic needs to know about the calendar or to represent the results in the way you want.

Adding/subtracting

to get a date N days before/after a given date [givendate] just use + and -:
[color green]
thirtydaysago: [givendate]-30
weekstime: [givendate]+7
[/color]
To get a date months or years in the past use DateAdd() as it knows how many days in each month/year:
[color green]
samedaylastmonth: DateAdd("m",-1,[givendate])
samedaylastyear: DateAdd("yyyy",-1,[givendate])
[/color]
You can also use DateAdd to add/subtract hours, minutes or seconds (the first argument is "n" for minutes"

DateAdd is described in the VBA help but not the Access help - open the VBA editor and look it up there

A related date

Use DateSerial() with the year(), month() and day() functions:
[color green]
firstofnextmonth: DateSerial(Year([givendate]),Month([givendate])+1,1)
lastoflastmonth: DateSerial(Year([givendate]),Month([givendate]),1)-1
[/color]
DateSerial works even if the month or day is <1, >12, >31 etc, it just adjusts everything logically to fit. E.g. month 0 is month 12 of the previous year.

Use DateAdd with year(), month() or day():
[color green]
lastofthismonth: DateAdd("m",1,[givendate])-day([givendate])
[/color]
Weeks

The function Weekday returns 1 to 7 depending of the day of the week a given date represents. This can be used with date arithmetic to give the previous Monday etc.
[color green]
previousmonday: [givendate]-Weekday([givendate],3)
[/color]
The second argument to Weekday() indicates the start day of the week, 1 or no argument is Sunday, 2 is Monday, 7 is Saturday.

These return true (-1) or false (0):
[color green]
isworkday: weekday([givendate],2)<6
isweekend: weekday([givendate],7)<3
[/color]
(thanks to Norris68 for the weekend tip)

Returning formatted dates

if you want to return a date as "Friday", "Tuesday" etc. use the Format() function:
[color green]
dayname: format([givendate],"dddd")
[/color]
To group records in months in a totals query add a field:
[color green]
YrMnth: format([givendate],"yyyymm")
[/color]
and group by that field.

Summing times

If you sum entries in a date/time field that represent times you get a numeric result, e.g. if you have

2:34
3:45
2:50

the sum comes to 0.38125. To get a H:M:S result you might use Format([mytimes],"hh:nn:ss") ("nn" for minutes not "mm" which is month) but this gives the hours modulo 24. This adds a position for the number of days if your sum is going to exceed 24 hours:
[color green]
dhhmmss: Str(Int(Sum([mytimes]))) & " " & Format(Sum([mytimes]),"hh:nn:ss")
[/color]
Good luck.