Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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]
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page