'=ColTime(Time1;Time2;Startrange;Endrange;GetTotalNumber)
'Startrange er området for møde*tider*.
'Endrange er området for "gå-hjem" *tider*.
'Time1 og Time2 er intervallet, der ønskes undersøgt. Højst 24 timer!
'Time1 og Time2 er cellereferencer til tider (18:15)
'eller funktionen TID(18;15;) indtastet direkte
'GetTotalNumber er valgfri (1 or 0 (standard)). Hvis den
'sættes til 1(én). returnerer funktionen det antal gange
'det søgte interval findes.

'Fra regnearket:
' =ColTime($F$2;$G$2;A2;C2)
'hvor fx F2=22:00 og G2=7:00
' =ColTime(TID(22;;);TID(7;;);A2;C2)
'Træk cellen ned med fyld-håndtaget

'Eller akkumuleret:
' =ColTime(F2;G2;A2:A100;C2:C100)
'hvor fx F2=22:00 og G2=7:00
' =ColTime(TID(22;;);TID(7;;);A2:A100;C2:C100)
'I denne situation er det ikke tilladt at have
'tomme celler omgivet af celler med indhold
'i A2:A100 og C2:C100
'

Function ColTime(Time1 As Double, _
Time2 As Double, StartRange As Object, _
Endrange As Object, _
Optional GetTotalNumber As Boolean) As Double
'leo heuser,May 1999/January 2002
Dim Cel As Object
Dim Dummy As Double
Dim EndTime As Double
Dim OffsetColumn As Long
Dim OffsetRow As Long
Dim StartTime As Double
Dim SubTime As Double
Dim TotalNumber As Long

OffsetRow = Endrange(1).Row - StartRange(1).Row
OffsetColumn = Endrange(1).Column - StartRange(1).Column
For Each Cel In StartRange.Cells
Dummy = 0
If Cel.Value = "" Then Exit For
StartTime = Cel.Value
EndTime = Cel.Offset(OffsetRow, OffsetColumn).Value
If StartTime = EndTime Then
If Time1 >= Time2 Then
Dummy = 1 - (Time1 - Time2)
TotalNumber = TotalNumber + 1
Else
Dummy = Time2 - Time1
TotalNumber = TotalNumber + 1
End If
ElseIf Time1 >= Time2 Then
If StartTime <= Time1 And StartTime >= Time2 Then
If EndTime >= Time1 Then
Dummy = EndTime - Time1
TotalNumber = TotalNumber + 1
ElseIf EndTime <= Time2 Then
Dummy = 1 - Time1 + EndTime
TotalNumber = TotalNumber + 1
ElseIf EndTime > Time2 And EndTime < StartTime Then
Dummy = 1 - Time1 + Time2
TotalNumber = TotalNumber + 1
End If
ElseIf StartTime <= Time2 Then
If EndTime >= Time2 And EndTime <= Time1 Then
Dummy = Time2 - StartTime
TotalNumber = TotalNumber + 1
ElseIf EndTime > StartTime And EndTime <= Time2 Then
Dummy = EndTime - StartTime
TotalNumber = TotalNumber + 1
ElseIf EndTime < StartTime Then
Dummy = Time2 - StartTime + 1 - Time1 + EndTime
TotalNumber = TotalNumber + 1
ElseIf EndTime >= Time1 Then
Dummy = Time2 - StartTime + EndTime - Time1
TotalNumber = TotalNumber + 1
End If
ElseIf StartTime >= Time1 Then
If EndTime > StartTime Then
Dummy = EndTime - StartTime
TotalNumber = TotalNumber + 1
ElseIf EndTime <= Time2 Then
Dummy = 1 - StartTime + EndTime
TotalNumber = TotalNumber + 1
ElseIf EndTime >= Time2 And EndTime <= Time1 Then
Dummy = 1 - StartTime + Time2
TotalNumber = TotalNumber + 1
ElseIf EndTime > Time1 And EndTime < StartTime Then
Dummy = 1 - StartTime + Time2 + EndTime - Time1
TotalNumber = TotalNumber + 1
End If
End If
ElseIf Time1 < Time2 Then
If StartTime <= Time1 Then
If EndTime < StartTime Or EndTime >= Time2 Then
Dummy = Time2 - Time1
TotalNumber = TotalNumber + 1
ElseIf EndTime >= Time1 And EndTime <= Time2 Then
Dummy = EndTime - Time1
TotalNumber = TotalNumber + 1
End If
ElseIf StartTime >= Time1 And StartTime <= Time2 Then
If EndTime <= Time1 Or EndTime >= Time2 Then
Dummy = Time2 - StartTime
TotalNumber = TotalNumber + 1
ElseIf EndTime < StartTime Then
Dummy = Time2 - StartTime + EndTime - Time1
TotalNumber = TotalNumber + 1
ElseIf EndTime <= Time2 Then
Dummy = EndTime - StartTime
TotalNumber = TotalNumber + 1
End If
ElseIf StartTime >= Time2 Then
If EndTime >= Time1 And EndTime <= Time2 Then
Dummy = EndTime - Time1
TotalNumber = TotalNumber + 1
ElseIf EndTime >= Time2 And EndTime < StartTime Then
Dummy = Time2 - Time1
TotalNumber = TotalNumber + 1
End If
End If
End If
SubTime = SubTime + Dummy
Next Cel
If GetTotalNumber Then
ColTime = TotalNumber
Else
ColTime = SubTime
End If
End Function

/Allan