Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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]
    Attached Images Attached Images

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brandy,

    Try this in your query field: Quantity: Format([TIME OUT]-[TIME IN],"Short Time")
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brandy,

    I thought this is what you wanted?
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I wanted it as a number, example 12.250000

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Attached Images Attached Images
    Regards
    John



  7. #7
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    John,

    Thank you very much!

  8. #8
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)
    Andrew

  10. #10
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you - that worked great!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

Posting Permissions

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