Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Sheet-Access 97

    I'm working on a payroll program for which I've also created a time sheet - due to hourly workers. It contains such
    fields as: StartTime, EndTime, LunchBreak and NetTime (which obviously should be calculated)

    Another problem is that I'm using marine time:

    08:30 - 17:00 which calcs out to 7.30

    but need to get this to 7.50 instead of 7.30 to use it to calculate pay, i.e., 7.50 x $16.00.

    Also, I've used

    Hours: Format([StartTime]-1-[EndTime],"Short Time")

    to calculate the difference between starting and ending times but can't get lunch break to work with it in the query (because Hours is calculated). I tried moving it out of the query and into the report but I still get #ERR messages.

    Seems like it should be very easy to do this but it just doesn't want to work for me.

    Any suggestions

    Thanks


    "CD"

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    In Access dates and times are actually stored as numbers.
    For instance:

    1/11/01 5:00 pm is stored as 36902.708
    1/11/01 10:30 pm is 36902.94

    The integer portion is the day and the decimal portion is the time. You can convert the dates/times to numbers subtract them, multiply by 24 and have the difference in hours:

    (cdbl(1/11/01 10:00 pm ) - cdbl(1/11/01 5:00 pm ))*24 = 5.5

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    this formula worked for me
    Text126 = (((((((Format(T_OUT, "hh") * 60) + Format(T_OUT, "nn")) * 60) + Format(T_OUT, "SS")) - ((((Format(T_IN, "hh") * 60) + Format(T_IN, "nn")) * 60) + Format(T_IN, "SS")))) / 60 ) / 60
    hope this helps
    JerryC

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Time Sheet-Access 97

    Hi CD,
    Can you explain further about your problem with lunch breaks - I didn't quite follow it. Thanks.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    Hi, Rory!

    The problem is I show:

    [StartTime[ 08:00 [EndTime] 16:30 which workds out to 8.30 hours. However, if I show [LunchBreak] .50 (1/2 hour) it obviously won't calculate properly so I would have to show lunch break as .30 - but then what do I do when lunch breark is 45 minutes? Hourly wage is $16.00.

    So, what I need to do is have a calculation that deducts ending time from starting time, less lunch break to arrive at regular hours which I then multiply by $16.00 or whatever the rate is. At this point if I have Hours: Format([StartTime]-1-[EndTime],"Short Time") in the query to get Hours but I can't use =[Hours]-[LunchBreak] in the query and when I attempt to do that in the report, it also errs out. So what I've been doing is just entering the number of hours in a field instead of letting the computer do it and then let that calculate against the rate.

    Does this explain (and can you help?)

    Thanks


    "CD"

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    Try this:

    Salary: Format(((CDbl([endtime])-CDbl([starttime]))*24-([Lunch]/60))*16,"$#.##")

    Where lunch is enter in minutes

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    Thanks for all the help; I'll give it a try.


    "CD"

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Time Sheet-Access 97

    Hi CD,
    cpod's posting will work perfectly (I think, didn't actually test it) but it might be useful to know that you can also do it this way:
    HrsWorked: DateDiff("h",[start_time],[end_time])-[lunch_break]*[hourly_rate]
    where I'm assuming that lunch_break is in hours (e.g. 0.5).
    If you want to see all the possibilities of DateDiff (very useful) the help is much better if you look it up in the VB editor environment rather than from a database window.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Sheet-Access 97

    Hi Rory, actually the both worked very well. Thanks to both of you.

    "CD"

Posting Permissions

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