Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Red face Unanswered: Sum of time over 24 hours

    I've got a table with all telephone details for the period of all personal. One field in this table is the time someone is on the phone for every phonecall (hh:mm:ss). Now I've got to make a query in which the total amount of time someone is on the phone is calculated. This is easily done with the "SUM"-function, but when the counter gets to 23:59:59 he gets to zero, in other words I can't calculate above 24 hours.

    Can anyone help me?

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32
    I'm not sure what data format you have the time stored as, but if its as a string, do the following;

    Extract the hour, minute and second figures seperately, proably by using the MID$ function.

    Multiple the mins figure by 60, and the hours figure by 3600, so everything is in seconds.

    Then do the SUM.

    The divide the result by 3600 to get the hours, minutes, secs etc, using the MOD and DIV functions.

    If you've got the value stored as a date, just use the SUM function o get the total time elapsed, then use the datediff function 3 times with 0 as the first date to get the hours, minutes, seconds.

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    I wonder if DateDiff includes time....

  4. #4
    Join Date
    Mar 2004
    Posts
    118

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Ok, I've done it with the DateDiff-function, thanks a lot.

    But I still have a question: when I divide the seconds by 3600 I get 9,663 is there a function that this gets 9 in stead of 10. i've tried INT, but this doesn't work Any idea?

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    try

    int = 1 to 9

  7. #7
    Join Date
    Apr 2004
    Posts
    3
    This seems to work,

    thanks a lot

Posting Permissions

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