Welcome to SPN

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

Sign Up Now!

Help! Calculating Date/Time

Discussion in 'Information Technology' started by Brandon Y, Nov 16, 2005.

  1. Brandon Y

    Brandon Y
    Expand Collapse
    Guest

    Hi I need help figuring out how to calculate time like a time tracking
    timeclock. I have the necessary table and fields already made out with data.
    Is there a way to calculate the time (i.e. the difference between clocking
    out and clocking in)? Are there any suggestions on how I should go about
    this? Please I need all the feedback and help I can get.
    thanks
     
  2. Loading...


  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    This will give you the difference in minutes --
    DateDiff("n",[clocking out], [clocking in])

    "Brandon Y" wrote:

    > Hi I need help figuring out how to calculate time like a time tracking
    > timeclock. I have the necessary table and fields already made out with data.
    > Is there a way to calculate the time (i.e. the difference between clocking
    > out and clocking in)? Are there any suggestions on how I should go about
    > this? Please I need all the feedback and help I can get.
    > thanks
     
  4. Brandon Y

    Brandon Y
    Expand Collapse
    Guest

    Where would I enter this information?

    "KARL DEWEY" wrote:

    > This will give you the difference in minutes --
    > DateDiff("n",[clocking out], [clocking in])
    >
    > "Brandon Y" wrote:
    >
    > > Hi I need help figuring out how to calculate time like a time tracking
    > > timeclock. I have the necessary table and fields already made out with data.
    > > Is there a way to calculate the time (i.e. the difference between clocking
    > > out and clocking in)? Are there any suggestions on how I should go about
    > > this? Please I need all the feedback and help I can get.
    > > thanks
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 15 Nov 2005 21:32:04 -0800, Brandon Y
    <BrandonY@discussions.microsoft.com> wrote:

    >Where would I enter this information?
    >
    >"KARL DEWEY" wrote:
    >
    >> This will give you the difference in minutes --
    >> DateDiff("n",[clocking out], [clocking in])
    >>


    Put the expression in a vacant Field cell in a query; or precede it by
    an = sign and use it as the Control Source of a textbox on a form or
    report.

    John W. Vinson[MVP]
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    In the design view of your query add in a blank column, FIELD row --
    Time Duration: DateDiff("n",[clocking out], [clocking in])

    "Brandon Y" wrote:

    > Where would I enter this information?
    >
    > "KARL DEWEY" wrote:
    >
    > > This will give you the difference in minutes --
    > > DateDiff("n",[clocking out], [clocking in])
    > >
    > > "Brandon Y" wrote:
    > >
    > > > Hi I need help figuring out how to calculate time like a time tracking
    > > > timeclock. I have the necessary table and fields already made out with data.
    > > > Is there a way to calculate the time (i.e. the difference between clocking
    > > > out and clocking in)? Are there any suggestions on how I should go about
    > > > this? Please I need all the feedback and help I can get.
    > > > thanks
     
  7. Brandon Y

    Brandon Y
    Expand Collapse
    Guest

    Well let me explain what I have and maybe you can help me out in the same
    terms or give me any feedback on how I should go about doing this...
    I have a table set up for ID#, Activity, Clock-In (check box), Clock-Out
    (check box), and Timestamp (=now()). How can I take each entry of the
    timestamp and figure out the difference in time to calculate the wage? The
    main issue is getting the calculations for total hours worked in a day, week,
    month.

    Thank you all for your help. I can feel that I am almost to a solution.

    "John Vinson" wrote:

    > On Tue, 15 Nov 2005 21:32:04 -0800, Brandon Y
    > <BrandonY@discussions.microsoft.com> wrote:
    >
    > >Where would I enter this information?
    > >
    > >"KARL DEWEY" wrote:
    > >
    > >> This will give you the difference in minutes --
    > >> DateDiff("n",[clocking out], [clocking in])
    > >>

    >
    > Put the expression in a vacant Field cell in a query; or precede it by
    > an = sign and use it as the Control Source of a textbox on a form or
    > report.
    >
    > John W. Vinson[MVP]
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 22:14:02 -0800, Brandon Y
    <BrandonY@discussions.microsoft.com> wrote:

    >Well let me explain what I have and maybe you can help me out in the same
    >terms or give me any feedback on how I should go about doing this...
    >I have a table set up for ID#, Activity, Clock-In (check box), Clock-Out
    >(check box), and Timestamp (=now()). How can I take each entry of the
    >timestamp and figure out the difference in time to calculate the wage? The
    >main issue is getting the calculations for total hours worked in a day, week,
    >month.
    >

    ok... I thought that the clockout and clockin times were in the same
    record. If you need to pull them from different records it's a bit
    harder!

    You'll need a "Self Join" query to pair each clockin with its
    corresponding clockout. This could be tricky - there's nothing in the
    database to prevent someone from clocking in Monday morning, not
    clocking out at all, clocking in again Tuesday, and clocking out
    Tuesday afternoon. This simple query will fail in that case (it will
    actually give the employee credit for 32 hours on Monday and 8 hours
    on Tuesday for example):

    SELECT Table.Timestamp AS TimeIn, Table_1.Timestamp As TimeOut,
    DateDiff("n", Table.Timestamp, Table_1.Timestamp) AS TimeOnJob
    FROM Table INNER JOIN Table AS Table_1
    ON Table.[ID#] = Table_1.[ID#]
    WHERE Table.ClockIn = True
    AND Table_1.ClockOut = True
    AND Table_1.Timestamp = (SELECT Min(X.[Timestamp]) FROM Table AS X
    WHERE X.[ID#] = Table.[ID#] AND X.ClockOut = True AND X.Timestamp >
    Table.Timestamp)
    WHERE Table.Timestamp >= [Enter start date and time:] AND
    Table.Timestamp <= [Enter end date and time:]

    This rather hairy query (which drastically needs testing, it's off the
    top of my head) finds for each Clock_in record the next corresponding
    Clock_Out record for that employee, and calculates the time difference
    in minutes. Meals and breaks aren't accounted for here, that's another
    issue.


    John W. Vinson[MVP]
     

Share This Page