Date Calculation in Microsoft Access

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]

 


Comments

Date Calculation in Microsoft Access — 7 Comments

  1. 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.

    • 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.

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *