# Thread: Elapse Time (Access XP)

1. ## 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. ## 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. ## 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. ## 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. ## 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?

6. ## 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. ## 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
•