Programming with Excel

[msmiley]

I have an Excel database with timestamps and I want to be able to count the number of times there is a lapse in a specified amount of time (ie., 5 mins.). Is there any way of doing this?

Please help!!!

 

[Guest_imported]

use the following function. Please note that this functions returns a text string therefore you can't use norman operator to work with the result. You can cast the result and work with that if you wish.

usage:

=timexx_subtract("01:04:02:52","00:00:00:70&quot
it will return 01:04:01:82

Ed

This function returns a text string, so you can't
Function TIMEXX_SUBTRACT(n1 As String, n2 As String, Optional fps As Double) As String
' Subtracts an TIMEXX code (n2) from an TIMEXX code (n1)
Dim h1 As Integer, m1 As Integer, s1 As Integer, x1 As Integer
Dim h2 As Integer, m2 As Integer, s2 As Integer, x2 As Integer
Dim h As Integer, m As Integer, s As Integer, x As Integer
Dim TotFrames As Double
Dim LeftOverFrames As Double
Dim fph As Double, fpm As Double
Dim NegFlag As Boolean, NegFlag1 As Boolean, NegFlag2 As Boolean
Dim Offset1 As Integer, Offset2 As Integer
Dim Frames1 As Double, Frames2 As Double

If fps = 0 Then fps = 100

' Treat blanks as zero
If n1 = "" Then n1 = "00:00:00:00"
If n2 = "" Then n2 = "00:00:00:00"

If Len(n1) = 12 Then
Offset1 = 1
NegFlag1 = True
Else
Offset1 = 0
NegFlag1 = False
End If

If Len(n2) = 12 Then
Offset2 = 1
NegFlag2 = True
Else
Offset2 = 0
NegFlag2 = False
End If

h1 = CInt(Mid(n1, 1 + Offset1, 2))
m1 = CInt(Mid(n1, 4 + Offset1, 2))
s1 = CInt(Mid(n1, 7 + Offset1, 2))
x1 = CInt(Mid(n1, 10 + Offset1, 2))

h2 = CInt(Mid(n2, 1 + Offset2, 2))
m2 = CInt(Mid(n2, 4 + Offset2, 2))
s2 = CInt(Mid(n2, 7 + Offset2, 2))
x2 = CInt(Mid(n2, 10 + Offset2, 2))

If m1 > 59 Or s1 > 59 Or x1 > fps - 1 Or m2 > 59 Or s2 > 59 Or x2 > fps - 1 Then
TIMEXX_SUBTRACT = Evaluate("#VALUE!&quot
Exit Function
End If

fph = fps * 60 * 60
fpm = fps * 60

Frames1 = (h1 * fph) + (m1 * fpm) + (s1 * fps) + x1
Frames2 = (h2 * fph) + (m2 * fpm) + (s2 * fps) + x2

TotFrames = Frames1 - Frames2
If TotFrames < 0 Then
NegFlag = True
TotFrames = Abs(TotFrames)
Else
NegFlag = False
End If

h = Int(TotFrames / fph)
If TotFrames >= fpm Then LeftOverFrames = (TotFrames Mod fph) Else LeftOverFrames = TotFrames

m = Int(LeftOverFrames / fpm)
If LeftOverFrames >= fpm Then LeftOverFrames = LeftOverFrames Mod fpm Else LeftOverFrames = LeftOverFrames

s = Int(LeftOverFrames / fps)
If LeftOverFrames >= fps Then LeftOverFrames = LeftOverFrames Mod fps Else LeftOverFrames = LeftOverFrames

x = LeftOverFrames

If NegFlag Then
TIMEXX_SUBTRACT = &quot;-&quot; & Format(h, &quot;00:&quot & Format(m, &quot;00:&quot & Format(s, &quot;00:&quot & Format(x, &quot;00&quot
Else
TIMEXX_SUBTRACT = Format(h, &quot;00:&quot & Format(m, &quot;00:&quot & Format(s, &quot;00:&quot & Format(x, &quot;00&quot
End If
End Function