Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Calculating Time in Hours/Minutes

    Here's my next problem guys...

    I want to calculate the time taken between Received and Closed (both are general date fields including the time 00:00:00)

    I am using the following code;
    Code:
    [TimeTaken] = DateDiff("h", Received, Closed)
    I can work out the hours if I have TimeTaken field as a number field but how would I allow it to display the difference in time (hh:mm)?


    And yes I have tried the following:
    [TimeTaken] = DateDiff("hh:mm", Received, Closed) (nor h,n h:n hh:nn)

    but that doesn't work?

    Also, what type should the TimeTaken field be - number or date field

    Thanks.
    Last edited by NeilMansell; 04-17-07 at 08:10.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    access stores date time values as integer=day,
    decimal=fraction of a day
    a second is 1/(24*60*60) = 0.00001157407407...... ish
    an hour is 1/(24*60) = 0.0416666667 ish
    so if you simply subtract the two values you have the time difference between the two events. this wokrs if the event duration staggers over more that one day

    use format (with an appropriate mask) to display the results
    the potentail problem with format is if you have more than 24 hours...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Assuming Received & Closed have a day part and can be different days then Time Taken is stored as this (does it need it be stored ?).
    [TimeTaken] = Closed - Received (as number/single)

    and displayed with this

    Int([TimeTaken]) & Format(([TimeTaken]) - Int([TimeTaken]), ":hh:mm:ss")

    This will display time taken in d:hh:mm:ss format.

    Does that help



    MTB

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    many thanks. followed your instructions and all works fine!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    TimeTaken = DateDiff("h", Received, Closed)
    
    NumberOfHours = Int(TimeTaken / 60 )
    NumberOfMinutes =  (TimeTaken % 60)
     'is it % or MOD in Access?
    Output = NumberOfHours & ":" & NumberOfMinutes
    How about something like that?
    EDIT: NumberOfMinutes will need rounding off or whatnot

    *shrug*
    George
    Home | Blog

Posting Permissions

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