Solved

Time Interval

Posted on 2002-04-04
10
252 Views
Last Modified: 2010-05-02
I am getting problem to compare two time with format(hh:mm:ss). I want to know what keyword or function can compare and give the difference between two time interval.
Like comparing 10.12.22 and 10.23.50. The keyword or function should give me the difference between the two times.

buy guy and goodluck.


0
Comment
Question by:mahenky
10 Comments
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 50 total points
ID: 6920154
Hi mahenky,

Use the DateDiff function in VB, example:

debug.print datediff("s","10.12.22","10.23.50")
0
 
LVL 6

Expert Comment

by:sharmon
ID: 6920161
Here is a routine that I wrote to do pretty much the same thing, you can adjust it to fit your needs.

Public Function DateDiffFractional(ByVal dtStart As Date, ByVal dtEnd As Date) As String
  Dim lngSecs As Long
  Dim lngYears As Long
  Dim lngDays As Long
  Dim lngHours As Long
  Dim lngMinutes As Long
  Dim strTemp As String
 
  If Not IsDate(dtStart) Or Not IsDate(dtEnd) Then
    'Enter your error handling here
    'Invalid date(s)
    Exit Function
  ElseIf CDate(dtEnd) < CDate(dtStart) Then
    'Enter your error handling here
    'Date end is less than date start
    Exit Function
  End If
 
  lngSecs = 0
  lngYears = 0
  lngDays = 0
  lngHours = 0
  lngMinutes = 0
 
  lngSecs = DateDiff("s", dtStart, dtEnd)
   
  If lngSecs >= 31536000 Then
    lngYears = Fix(lngSecs / 31536000)
    lngSecs = lngSecs - (lngYears * 31536000)
  End If
 
  If lngSecs >= 86400 Then
    lngDays = Fix(lngSecs / 86400)
    lngSecs = lngSecs - (lngDays * 86400)
  End If
 
  If lngSecs >= 3600 Then
    lngHours = Fix(lngSecs / 3600)
    lngSecs = lngSecs - (lngHours * 3600)
  End If
 
  If lngSecs >= 60 Then
    lngMinutes = Fix(lngSecs / 60)
    lngSecs = lngSecs - (lngMinutes * 60)
  End If
 
  If lngYears > 0 Then strTemp = "Years: " & lngYears & " "
  If lngDays > 0 Then strTemp = strTemp & "Days: " & lngDays & " "
  If lngHours > 0 Then strTemp = strTemp & "Hours: " & lngHours & " "
  If lngMinutes > 0 Then strTemp = strTemp & "Minutes: " & lngMinutes & " "
  If lngSecs > 0 Then strTemp = strTemp & "Seconds: " & lngSecs
 
  DateDiffFractional = Trim(strTemp)
End Function
0
 
LVL 6

Expert Comment

by:sharmon
ID: 6920166
BTW: DateDiff will work fine as a single line function if you only need the seconds, else you will have to convert the hours and minutes yourself from the seconds...
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6920271
sharmon, very true but here is a neat trick to get this done automatically:

Debug.Print format(dateadd("s",datediff("s","10:12:22","10:23:50") ,format(now(),"yyyy-mm-dd")),"ttttt")
0
 
LVL 6

Expert Comment

by:sharmon
ID: 6921212
Tim,

That's very cool, still left a little formatting, but a cool trick.

As well, that function I gave above can also be shortened down to this for just times...

Public Function TimeDiff(ByVal Time1 As Date, ByVal Time2 As Date) As String
    Dim intHours As Integer
    Dim intMinutes As Integer
    Dim intSeconds As Integer
   
    intSeconds = Abs(DateDiff("s", TimeValue(Time1), TimeValue(Time2)))
   
    If intSeconds >= (60 * 60) Then
        intHours = Fix(intSeconds / (60 * 60))
        intSeconds = intSeconds - (intHours * (60 * 60))
        TimeDiff = Format$(intHours, "00") & ":"
    End If
       
    If intSeconds >= 60 Then
        intMinutes = Fix(intSeconds / 60)
        intSeconds = intSeconds - (intMinutes * 60)
        TimeDiff = TimeDiff & Format$(intMinutes, "00") & ":"
    End If
   
    TimeDiff = TimeDiff & Format$(intSeconds, "00")
End Function
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:RichW
ID: 6921559
Here's a simpler one:

Public Function TimeDiff(ByVal Interval As String, ByVal Time1 As String, ByVal Time2 As String)
   
    Dim HourTime1, HourTime2, MinuteTime1, MinuteTime2, SecondTime1, SecondTime2, TimeDifference
    Dim Time1InSeconds, Time2InSeconds
    Time1 = FormatDateTime(Time1, vbLongTime)
    Time2 = FormatDateTime(Time2, vbLongTime)
    HourTime1 = Hour(Time1)
    HourTime2 = Hour(Time2)
    MinuteTime1 = Minute(Time1)
    MinuteTime2 = Minute(Time2)
    SecondTime1 = Second(Time1)
    SecondTime2 = Second(Time2)
    Time1InSeconds = SecondTime1 + (MinuteTime1 * 60) + (HourTime1 * 3600)
    Time2InSeconds = SecondTime2 + (MinuteTime2 * 60) + (HourTime2 * 3600)
    TimeDifference = Time2InSeconds - Time1InSeconds
    Select Case Interval
        Case "h"
            TimeDifference = TimeDifference / 3600
        Case "m"
            TimeDifference = TimeDifference / 60
    End Select
    TimeDiff = TimeDifference
End Function

Call it by:
timeNow = Now
timeDifferenceHours = TimeDiff("h", timeStart, timeNow)
timeDifferenceMinutes = TimeDiff("m", timeStart, timeNow)
timeDifferenceSeconds = TimeDiff("s", timeStart, timeNow)

0
 
LVL 6

Expert Comment

by:sharmon
ID: 6922061
RichW,

How is that simpler and what's the major difference between using what you have there and just the built in DateDiff function?

Debug.Print DateDiff("s", "10:12:22","10:23:50")

I suppose your function would give you fractions of an Interval if needed.

Regards,
Shannon
0
 
LVL 4

Expert Comment

by:RichW
ID: 6922241
I thought he wanted just time.

My apologies, Sharmon.  I saw 43 lines of code and missed your last posting. The overhead in If statements and DateDiff's is made up by my variant declarations.  lol

Must be all the beer I had last night.


Regards,
RichW

0
 
LVL 6

Expert Comment

by:sharmon
ID: 6922387
It's no problem, just thought maybe I was missing something there in your code.

Watch all that beer, you need to save some for the rest of us.

Shannon
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7642937
Hi mahenky,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept ryancys's comment(s) as an answer.

mahenky, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now