Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    time calculation (97)

    Hi all, could someone please offer a solution to calculation time difference (in hours) between two dates? thanks, Van [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    New Lounger
    Join Date
    Dec 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time calculation (97)

    Hi Van,

    This function calculates the elapsed time between two values and
    formats the result in four different ways.

    The function accepts interval arguments such as the following:
    #12/15/02 6:00:00AM# - #12/15/02 10:00:00PM#

    -or-

    [End Time]-[Start Time]

    ' Called by ElapsedTime(#12/15/2002 11:23:00 PM# - #12/15/2002 11:12:12 AM#)
    Function ElapsedTime(interval)
    Dim x
    x = Int(CSng(interval * 24 * 3600)) & " Seconds"
    Debug.Print x
    x = Int(CSng(interval * 24 * 60)) & ":" & Format(interval, "ss") _
    & " Minutes:Seconds"
    Debug.Print x
    x = Int(CSng(interval * 24)) & ":" & Format(interval, "nn:ss") _
    & " Hours:Minutes:Seconds"
    Debug.Print x
    x = Int(CSng(interval)) & " days " & Format(interval, "hh") _
    & " Hours " & Format(interval, "nn") & " Minutes " & _
    Format(interval, "ss") & " Seconds"
    Debug.Print x

    End Function

    Best,

    Bob

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time calculation (97)

    If all you need is the elapsed hours between two Date/Time values ... then all you need is to use is the DateDiff() Function:

    DateDiff("h", [YourStartValue], [YourEndValue])

    HTH
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: time calculation (97)

    I have the same question - except I want my time difference to show hours and minutes - i.e., time in is 8:00 am and time out is 9:15 am I want total time to reflect 1.25 hours. this is in query and form.

    thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: time calculation (97)

    Dates and times are stored in Access in the form of numbers. The integer part is the number of days since December 31, 1899 and the fractional part is the time of day (midnight is .0, 6 AM is .25, noon is .5 etcetera).

    If you subtract two dates/times, you get a number that represents a number of days; to get the number of hours, multiply this by 24. You can check this by opening the Debug window Ctrl+G) and typing
    ? (#9:15#-#8:00#)
    You'll get 5.20833333E-2 meaning 0.052083333 - the difference is a bit more than 0.05 day.
    If you type
    ? (#9:15#-#8:00#)*24
    you'll get 1.25

    In a query, you can use
    Difference: ([EndTime]-[StartTime])*24

    In a form or report, you can set the Control Source of a text box to
    =([EndTime]-[StartTime])*24

    In both cases, you can set the Format property to display the number of decimals you want.

Posting Permissions

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