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

    Elapse Time (Access XP)

    I've seen some of the posts in this forum on calculating elapsed time but none of them seem to do what I need, which is fairly simple in Excel.

    Give a 9:00 A.M. starting time and a 5:00 P.M. quitting time (most days), I just want Access to calculate the number of hours worked each day, less an hour for lunch, and with the ability to calculate correctly if someone starts 15 minutes early or perhaps works an extra half hour overtime at night.

    So, in Excel I have a formula that looks like:

    =12-B15+C15-1 or =12-[Start Time]+[End Time]-1 and it works perfectly. Why doesn't that work in Access? I don't want to use Visual Basic, just an express as the Control Source for the text box on the report or form.

    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Elapse Time (Access XP)

    As long as your start and end times are defined as short time you could use:

    =11+DateDiff("h",[textStartTime],[textEndTime])

    I have put this in a form and it works ok for me, the only problem is that it works out the hours as an integer.

  3. #3
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Elapse Time (Access XP)

    You could also use this which will display the the actual time difference:

    Format([Start Time]-1-[End Time],"Short Time")

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

    Re: Elapse Time (Access XP)

    The Excel formula you mention would only work if you entered the starting time and quitting time as numbers, not as time values. For example, 9:30 would have to be entered as 9.5 and 4:45 as 4.75.
    Moreover, the formula wouldn't yield the correct result if someone took half a day off and worked from, say, 1:00 PM to 5:00 PM.

    If you are prepared to live with the same limitations in Access, the expression would be similar: with Number, Single precision fields StartTime and QuitTime, the expression would be

    =12-[StartTime]+[QuitTime]-1

    But it would be far better (in Excel and in Access) to use time values instead of numbers. Both Excel and Access internally store time values as a fraction of 1 day; you can simply subtract two time values. To subtract 1 hour, use 1/24 (of a day):

    =[QuitTime]-[StartTime]-1/24

    The result will automatically be displayed as a time, and you can determine the exact display format by setting the Format property.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Elapse Time (Access XP)

    Exactly what are you looking for? Do you want to show the result in Hours and Minutes, or in decimal hours? That is, do you want to show the result of working 15 minutes longer than the regular 9-5 as 7:15 or 7.25?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Elapse Time (Access XP)

    That works. What would I do to it if I wanted to automatically subtract out an hour for lunch?

    Thanks.

  7. #7
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Elapse Time (Access XP)

    This will give you the correct time if your time spans crosses to the next day:

    Format([Start Time]-1-[End Time]+1/24,"Short Time")

    This will give you the correct time if your time will always be calculated for the same day only:

    Format([End Time]-[Start Time]-1/24,"Short Time")

Posting Permissions

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