Time interval overlap with other time interval

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am trying to evaluate a list of arrival and departure times to see if they fall into the range of a list of time intervals. The list of time intervals, designates a condition of 1, else it is condition 0. I want to evaluate each arrival and departure singly to the list of all time intervals. They arrival/departure and the intervals are on different worksheets. I am not sure if a simple formula will work or do I need to write a macro

Example data on one worksheet....it is 49,000 entries in realit
ARRIVAL DEPARTUR
1/8/01 22:10 1/9/01 1:1
1/8/01 22:15 1/9/01 0:5
1/8/01 22:16 1/9/01 3:0
1/8/01 22:16 1/9/01 0:3
1/8/01 22:32 1/9/01 0:5
1/8/01 22:54 1/9/01 0:3
1/8/01 23:20 1/9/01 1:1
1/8/01 23:47 1/9/01 1:4
1/9/01 0:19 1/9/01 1:5
1/9/01 0:20 1/9/01 2:0
1/9/01 1:26 1/9/01 3:0
1/9/01 2:04 1/9/01 4:0
1/9/01 2:12 1/9/01 7:3
1/9/01 2:17 1/9/01 3:2
1/9/01 2:21 1/9/01 12:1
1/9/01 2:43 1/9/01 10:3
1/9/01 2:50 1/9/01 3:3
1/9/01 3:25 1/9/01 4:1
1/9/01 3:27 1/9/01 4:1
1/9/01 4:24 1/9/01 9:2
1/9/01 4:31 1/9/01 6:4
1/9/01 4:39 1/9/01 5:4
1/9/01 4:41 1/9/01 7:0
1/9/01 4:50 1/9/01 9:0
1/9/01 5:20 1/9/01 12:5
1/9/01 6:21 1/9/01 7:4
1/9/01 6:42 1/9/01 7:3
1/9/01 7:28 1/9/01 14:3
1/9/01 7:32 1/9/01 8:4

time Interval dat

Date/time On Date/time Of
1/8/01 20:47 1/9/01 0:0
2/15/01 21:52 2/16/01 11:0
2/16/01 17:13 2/17/01 7:3
2/18/01 15:12 2/18/01 16:1

Any help would be grea
 
Hi
not quite sure but if col. A stores the arrivala date/time and col. B
the departure date/time enter the following in C1
=IF(SUMPRODUCT(--('interval_data'!$A$1:$A$10000<=A1),--('interval_data'
!$B$1:$B$10000>=A1))+SUMPRODUCT(--('interval_data'!$A$1:$A$10000<=B1),-
-('interval_data'!$B$1:$B$10000>=B1))>0,1,0)
and copy this down
 
Below is the gist of a suggestion to you
in your previous post in .misc, ref:

http://tinyurl.com/2tucs

to which you did not provide any feedback ..
[so I'm not sure whether we connected that time?]

---
Maybe take a look at this previous response ?

http://tinyurl.com/2r7gl
(Subject: Ward occupancy chart)

which is quite relevant and works on
data with structure similar to what you posted

The "Occupancy" derivation in the thread
would be equivalent to your desired "# in system"

If you're interested to have the sample book
(Tracking of Occupancy per hourly band)
just post a "readable" email address here
and I'll send it via private email

Sample is easily adaptable to suit

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Meggie said:
Hello,

I am trying to evaluate a list of arrival and departure
Click to expand...
times to see if they fall into the range of a list of time
intervals. The list of time intervals, designates a
condition of 1, else it is condition 0. I want to
evaluate each arrival and departure singly to the list of
all time intervals. They arrival/departure and the
intervals are on different worksheets. I am not sure if a
simple formula will work or do I need to write a macro.
 
Back
Top