1. I am trying to calculate the time worked in access 2007. I have a column for start time and end time in a table. Separately in a query I have added a expression column that contains the following: Quanity: [TIME OUT]-[TIME IN]. If I have a 6 am start time and 6 pm end time, using short date, you would think you would get 12 but I am getting .5 instead.

[attachment=89600:New Picture (2).bmp]

2. Brandy,

Try this in your query field: Quantity: Format([TIME OUT]-[TIME IN],"Short Time")

3. I need to change this from short time to a number with 6 decimal places. I have used the following formula but get 8.749992.

Quantity2: Format([TIME OUT]-[TIME IN],"#.000000")*24

4. Brandy,

I thought this is what you wanted?

5. I wanted it as a number, example 12.250000

6. I would subtract the times, multiply by 24, then round the result to the required number of places.

Hours: Round(([Ftime]-[Stime])*24,2)

[attachment=89608:workhours.gif]

What is going on is that Dates and Times are stored as numbers. Whole Numbers represent days, and the decimal part represent the time.

7. John,

Thank you very much!

8. If I have a person that works from 18:00 to 6:00 it shows up as -12, is there something I can do so that it calculates the change in days.

9. You need a different calculation.
Easiest way is probably with an IIF function to check if Finish Time is Less than Start Time

Round((IIF([Ftime]<[Stime],[Ftime]+(1-[Stime]),[Ftime]-[Stime]))*24,2)

10. Thank you - that worked great!

11. Just so you understand what is going on here - working from 18:00 to 06:00 then what that really means is that the person worked from Day1 18:00 to Day 2 06:00. So today, 12th August 2010 happens to be day 40402 (you can check it out by going to the VBA immediate window and typing ? Clng(date())), so if that person finished at 06:00 today, then they started work at 40401.75 and finished work at 40402.25.

Your current solution works as long as someone doesn't work more than 24 hours in one shot, but if they work 25 you will get a value of 1 hour. In general I think you are better off to store the value as a full date/time value and do your math on it. See the thread Subtracting Time for a discussion last year on a similar topic.

#### Posting Permissions

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