Eliminate Overlapping Entries
 

I have a file with multiple rows of time "in" and time "out" entries. I
am trying to get a grand total of time entered. The problem is that
some entries overlap with others. Here is an example:

20060201 10:00 AM 11:00 AM 10:00 11:00 1:00
20060201 9:30 AM 10:15 AM 9:30 10:15 0:45
20060201 9:30 AM 9:45 AM 9:30 9:45 0:15

All of these entries occured on 2/1/06 so they are overlapping each
other. The actual total time should be 1:30 (9:30 AM - 11:00 AM). Is
there a way to avoid a result of 2:00?

Thanks

Mark


--
Mark@Marc
------------------------------------------------------------------------
Mark@Marc's Profile: http://www.excelforum.com/member.php...o&userid=28126
View this thread: http://www.excelforum.com/showthread...hreadid=513668

Eliminate Overlapping Entries
 

Mark,

You can use a user-Defined-Function, modified from code by Bob Phillips. Works fine for times on one
day.

Copy the code below into a codemodule, then use it like

=SumTime(A2:B10)

and format the cell for time, to get 1:30.

(Or, use it like

=SumTime(A2:B10)*24

and format for decimal, to get 1.50)

HTH,
Bernie
MS Excel MVP

Function SumTime(myTimes As Range) As Variant
Dim arySet(1 To 1440) As Integer
Dim rngCount
Dim i As Long, j As Long
Dim nTime As Double

If myTimes.Columns.Count < 2 Then
SumTime = "Too many columns"
Exit Function
End If

rngCount = myTimes.Rows.Count
For i = 1 To rngCount
For j = myTimes(i, 1).Value * 1440 To _
myTimes(i, 2).Value * 1440 - 1
arySet(j) = 1
Next j
Next

For i = 1 To 1440
nTime = nTime + arySet(i)
Next

SumTime = nTime / 1440

End Function


"Mark@Marc" wrote in message
...

I have a file with multiple rows of time "in" and time "out" entries. I
am trying to get a grand total of time entered. The problem is that
some entries overlap with others. Here is an example:

20060201 10:00 AM 11:00 AM 10:00 11:00 1:00
20060201 9:30 AM 10:15 AM 9:30 10:15 0:45
20060201 9:30 AM 9:45 AM 9:30 9:45 0:15

All of these entries occured on 2/1/06 so they are overlapping each
other. The actual total time should be 1:30 (9:30 AM - 11:00 AM). Is
there a way to avoid a result of 2:00?

Thanks

Mark


--
Mark@Marc
------------------------------------------------------------------------
Mark@Marc's Profile: http://www.excelforum.com/member.php...o&userid=28126
View this thread: http://www.excelforum.com/showthread...hreadid=513668

Eliminate Overlapping Entries
 

Or if you prefer a non-VBA solution
and the simplified data looks like this:
start stop
0:07 0:13
0:15 0:22
0:19 0:24
0:30 0:40
0:32 0:37

sum
0:25
0:25

Name your data with the headers indicated.
Use Insert Name Define
Add the following names:
kt Refers To =1440
seqm Refers To =ROW(INDEX(C100,1):INDEX(C100,kt))
seqc Refers To =COLUMN(INDEX(R100,1):INDEX(R100,COUNTA(start)))
mulp Refers To =ROW(INDEX(C100,1):INDEX(C100,COUNTA(start)))^0
array1 Refers To
=--NOT((seqm=INDEX(start*kt,seqc))*(seqm<INDEX(stop* kt,seqc)))
sum_ovlp Refers To =1-SUM(--(MMULT(array1,mulp)=COUNTA(start)))/kt
Below sum, select the 2 cells and enter with Cntrl+Shift+Enter
=sum_ovlp
and format as time.
All formulas are in R1C1 style.
Check R1C1 in Tools Options General
Un-check after completing formula entry.

Eliminate Overlapping Entries
 

Thanks guys for the replies. I decided to go with the non-VBA solution.
I ran into a problem though. I am stuck on the part where I select the
2 cells and enter Ctrl+Shift+Enter. When I hold these keys down
simultaneously I am not able to enter =sum_ovlp. Also, do I check R1C1
before I enter everything and then uncheck it once everything is
entered?

Thanks


--
Mark@Marc
------------------------------------------------------------------------
Mark@Marc's Profile: http://www.excelforum.com/member.php...o&userid=28126
View this thread: http://www.excelforum.com/showthread...hreadid=513668

Eliminate Overlapping Entries
 

Select the 2 cells.
Go to the formula bar and type in
=sum_ovlp
Then press the three keys (Cntl, Shift, Enter) simultaneously
instead of simply pressing Enter.
You stated the R1C1 procedure correctly.
There is more info at Help Array formula.