Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Calculating Total Hours

    HI,

    I've been working on this problem for what seems a life time.

    I have a simple table that requires a start time, finish time and total hours.

    However the problems I'm having is that the hours can run from one day to the next. so a simple formula to take one from the other would not work.

    Does anybody know a formula I can use to do this calculation, because I have tried all sorts and not being very experienced with the use of Access I'm getting myself more into a mess.

    Thanks for your time.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Calculating Total Hours

    Originally posted by paulfish
    HI,

    I've been working on this problem for what seems a life time.

    I have a simple table that requires a start time, finish time and total hours.

    However the problems I'm having is that the hours can run from one day to the next. so a simple formula to take one from the other would not work.

    Does anybody know a formula I can use to do this calculation, because I have tried all sorts and not being very experienced with the use of Access I'm getting myself more into a mess.

    Thanks for your time.
    Use the DateDiff function in a query. Ex. TotalHours:DateDiff("h",

    StartDateandTime, EndDateandTime). The "h" calculates the difference

    in hours. I tried it and it worked for me.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Re: Calculating Total Hours

    Originally posted by basicmek
    Use the DateDiff function in a query. Ex. TotalHoursateDiff("h",

    StartDateandTime, EndDateandTime). The "h" calculates the difference

    in hours. I tried it and it worked for me.

    Hope this helps.

    Gregg
    Hi Gregg,

    Thanks for your reply I have got this to work, however it only seems to work if my fields are set to general dates, do you know if there is a way of making this work should I use seperate fields for both date and time. I have tried doing this but with no success.

    Thanks again for your time in helping me out.

    Regards

    Paul

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    If you use separate fileds for dates and times why not concat them together and use CDate to return a timestamp for your hours difference using DateDiff ...

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by M Owen
    If you use separate fileds for dates and times why not concat them together and use CDate to return a timestamp for your hours difference using DateDiff ...
    Hi

    Thanks for the reply. I have sorted the issue of dates and times now my only proble is when using datediff it only gives me the hours not the minutes as well. I have tried putting in an "m" value with not success is it possible as the help files give me no help on what values i can use within the function.

    Thanks

    Paul.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Paul,

    Go down to the minutes and then do the calculation yourself ... DateDiff using minutes returns the minutes between the 2 dates ... ALL of them. So just div by 60 to get your hours and the remainder is just that ...

  7. #7
    Join Date
    Oct 2003
    Posts
    4
    Originally posted by M Owen
    Paul,

    Go down to the minutes and then do the calculation yourself ... DateDiff using minutes returns the minutes between the 2 dates ... ALL of them. So just div by 60 to get your hours and the remainder is just that ...
    Hi

    Thanks for your reply, I've now finally sorted it.

    Thanks for your help.

    Kind regards

    Paul.

Posting Permissions

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