Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date/Time Calculation

Discussion in 'Information Technology' started by Robb @ FLW, Jul 28, 2006.

  1. Robb @ FLW

    Robb @ FLW
    Expand Collapse
    Guest

    I have developed a Time Tracker in my query I have [Date Time Out] and [Date
    Time In] appears like this
    date time out date time in
    2/19/2006 10:30:00 AM 2/20/2006 12:45:00 AM.

    Under data type in my table I am using Date/Time with a format of General
    Date.

    I want this to be totaled in my [Hours] field, sometime will go after
    midnight.

    How can I get this to calculate the [Hours] in my query?
    --
    Robb
     
  2. Grinder

    Grinder
    Expand Collapse
    Guest

    Robb @ FLW wrote:
    > I have developed a Time Tracker in my query I have [Date Time Out] and [Date
    > Time In] appears like this
    > date time out date time in
    > 2/19/2006 10:30:00 AM 2/20/2006 12:45:00 AM.
    >
    > Under data type in my table I am using Date/Time with a format of General
    > Date.
    >
    > I want this to be totaled in my [Hours] field, sometime will go after
    > midnight.
    >
    > How can I get this to calculate the [Hours] in my query?


    Hours = 24 * (DateOut - DateIn)

    DateTime types are just Doubles that count a number of days from a fixed
    "zero date." So for example, if you have a date, and you want to add 6
    hours, you can just add 0.25, or 1/4 of a day.
     
  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 13 Jun 2006 11:13:02 -0700, Robb @ FLW
    <RobbFLW@discussions.microsoft.com> wrote:

    >I have developed a Time Tracker in my query I have [Date Time Out] and [Date
    >Time In] appears like this
    >date time out date time in
    >2/19/2006 10:30:00 AM 2/20/2006 12:45:00 AM.
    >
    >Under data type in my table I am using Date/Time with a format of General
    >Date.
    >
    >I want this to be totaled in my [Hours] field, sometime will go after
    >midnight.
    >
    >How can I get this to calculate the [Hours] in my query?


    I'd suggest

    DateDiff("h", [Date Time Out], [Date Time In])

    to get integer hours; for hours and fractions of an hour, use

    DateDiff("n", [Date Time Out], [Date Time In]) / 60

    to calculate the time in miNutes ("m" is Months) and divide.

    John W. Vinson[MVP]
     

Share This Page