Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Days and hours difference b/w two date/time fields (access 2000)

    I am trying to calculate the difference in days and hours between two date and time fields. I am a newbie at this and don't event know where to start. My field names are date1, time 1, date 2, time 2. If I was doing this in Excel I would combine each date and time in one cell then type (Cell 2 - Cell 1) * 24. How do I do this in access?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Days and hours difference b/w two date/time fields (access 2000)

    You need to use the DateDiff function -- search the Help for the exact syntax. You can use it either in a query to create a new column, or as the controlsource of a form or a report. DateDiff will return the difference in days, hours, or whatever other unit you choose.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Days and hours difference b/w two date/time fields (access 2000)

    If you want to calculate the time difference in a query, you can enter a calculated expression in an empty field in the query grid:

    TimeDifference: ([Date 2]+[Time 2]-[Date 1]-[Time 1])*24

    If you want days and hours separately, create two calculated fields:

    DaysDifference: [Date 2]-[Date 1]-IIf([Time 2]<[Time 1], 1, 0)
    HoursDifference: (IIf([Time 2]<[Time 1], 1, 0)+[Time 2]-[Time 1])*24

    If you want to show the difference on a form without calculating it in a query, use text boxes with control source like

    =([Date 2]+[Time 2]-[Date 1]-[Time 1])*24

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •