mahenky
asked on
Time Interval
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
sharmon, very true but here is a neat trick to get this done automatically:
Debug.Print format(dateadd("s",datedif f("s","10: 12:22","10 :23:50") ,format(now(),"yyyy-mm-dd" )),"ttttt" )
Debug.Print format(dateadd("s",datedif
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
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
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)
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)
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
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
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
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
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
Watch all that beer, you need to save some for the rest of us.
Shannon
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
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
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