There are a number of ways to calculate the time between two dates in Access. You can use a calculated field in a query, a calculated control in a form or report, or you can use a VBA procedure to perform the calculation.
If you need to calculate the number of days between two dates, the syntax is:
=[One date field] - [Another date field]
You can use functions to calculate the number of years between two dates:
=Year([One date field]) - Year([Another date field])
You can also use the DateDiff() function, which uses an argument to determine how the time interval is measured.
For example,
=DateDiff("q", Date1, Date2)
returns the number of quarters between the two fields. (In the example above, Date1 would be subtracted from Date2.) The other intervals that can be used in this expression are as follows: “yyyy” – Years, “m” – Months, “y” – Day of Year, “d” – Day, “w” – Weekdays, “ww” – Weeks, “h” – Hours, “n” – Minutes, “s” – Seconds.
Naming Date Fields
When naming date fields in Access, avoid naming a field Date, since this is a built-in Access function. Instead, consider using more descriptive names, such as BeginDate, HireDate, etc.
Hard Coding a Date
When calculating with dates in Access, you can hard-code an arbitrary date by using the following syntax:
#mm/dd/yyyy#
For example, you would use the following code to subtract a date from Jan. 1, 2006:
=#1/1/2006# - [Date Field]
Thank you for the clear explanation and examples, really helped me out.
i have a problem on Calculating Workin Hours
Time out – time in
for 10:00 pm – 6:00 am =16 and not 8 Hrs, hou to display Hour and Minute?
I need to create a criteria to provide the following results:
If pri = E (48) then add 48 hours to the Received Date. This will populate the Submission Date.
Not sure of your formula – what is “pri=E(48)”? To add 48 hours to the received date, add 2 to the date. If the date is in E4, then =E4+2 will update the date in a separate cell. To change the value in that cell, you need a macro, along the lines of:
sub AddHours(tmpDate as date)
if [your test] then
tmpDate = tmpDate + 2
[cell reference].value = tmpDate
end if
end sub
Can’t give you more without knowing more about your test and spreadsheet.
how will be the result “5 years 3 months and 21 days”
What are you asking? You can calculate the number of days between two dates, but cannot return the number of years, months, and days, as far as I know.
Hai .i need help .i have two fields in table records one is start date and end date .Now i need to add another field like No of days required .So i need difference of above two field.if i use the this expression =[start date]-[End date] it showing wrong value