Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timecard application...adding up timestamps (2003)

    I am working on a timeclock application for a friend but I have reached a point where I am stumped.

    The users will "clock in" 2-6 times per day in pairs. If you clock in, you must clock out.

    What I am needing to do is:
    * Convert a date/time value like "6/19/2007 6:06:59 PM" to something like this "18.11"
    * Subtract the clock ins from the clock outs for each day and get the total hours worked, to the nearest hundredth.

    Any suggestions?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Timecard application...adding up timestamps (2003)

    Create a totals query.
    Add the employee and date fields (both with Group By)
    Create a calculated field to subtract each clock in time from the corresponding clock out time, and multiply the difference with 24 (the number of hours in a day).
    Set the Total option for this column to Sum.
    Specify the desired format for this column.

    BTW, were the replies to <post#=649196>post 649196</post#> and <post#=649378>post 649378</post#> helpful? Since you didn't provide feedback, other Loungers reading those threads don't know whether the problem is solved.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timecard application...adding up timestamps (2

    My table is not really designed to do what you are suggesting, unless I just don't know how to do it.

    See attached screenshot for table. I = in, O = out.

    I'm open to redesign suggestions, as this is still in the early phase.

    Thanks.

    PS: I had forgotten about those other posts, so I just posted to them with my resolutions. Thanks for the reminder.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Timecard application...adding up timestamps (2

    See the attached small demo.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timecard application...adding up timestamps (2

    That's awesome, thank you.

    I just thought of another issue...If they clock in before midnight but out after, how do I handle that? (Does this warrant a new thread?)
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Timecard application...adding up timestamps (2

    > Does this warrant a new thread?

    No, it's a follow-up question, not an entirely new one, so you were correct to post it here.

    Can you draw a line, e.g. all times before 4 AM should be counted as belonging to the previous day?
    Or do we have to find out which "out" time belongs to which "in" time?

  7. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timecard application...adding up timestamps (2

    They are open until 1 am, so anything before 3 am is guaranteed to be prior day's business.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Timecard application...adding up timestamps (2

    Change the definition of TheDate in both qryIn and qryOut to
    <code>
    TheDate: Int(DateAdd("h",-3,[TimeStamp]))
    </code>
    This subtracts 3 hours from the date/time before determining the date, so for example June 21, 2:00 AM becomes June 20, 11:00 PM. This causes the time to be seen as belonging to June 20.

  9. #9
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timecard application...adding up timestamps (2

    That worked like a charm, thank you so much!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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