Time query

[Ber]

Is it possible to work out from this data how many extension Nos are on a
call at the same time on the same date. This is just a sample the entire
sheet covers a full year of calls with almost 8000 rows of data. Thank You.
A B C D
E
Ext NO Date (dd/MM/yyyy) Start Time Duration End Time
3224 01/11/2007 08:47:00 0:00:28 08:47:28
3336 01/11/2007 09:25:00 0:00:34 09:25:34
3219 01/11/2007 09:51:00 0:00:33 09:51:33
3278 01/11/2007 10:07:00 0:00:25 10:07:25
3016 01/11/2007 10:19:00 0:00:50 10:19:50
3161 01/11/2007 10:32:00 0:00:00 10:32:00
3161 01/11/2007 10:37:00 0:00:00 10:37:00
3311 01/11/2007 11:48:00 0:00:03 11:48:03
3318 01/11/2007 11:54:00 0:00:04 11:54:04
3133 01/11/2007 12:11:00 0:00:37 12:11:37
3318 01/11/2007 12:22:00 0:00:21 12:22:21
3133 01/11/2007 12:23:00 0:00:00 12:23:00
3133 01/11/2007 12:27:00 0:00:04 12:27:04
3133 01/11/2007 12:29:00 0:01:59 12:30:59
3133 01/11/2007 14:25:00 0:00:53 14:25:53
3380 01/11/2007 14:58:00 0:00:16 14:58:16
3333 01/11/2007 15:10:00 0:03:05 15:13:05
3380 01/11/2007 15:12:00 0:00:26 15:12:26
3133 01/11/2007 16:28:00 0:03:42 16:31:42
3133 01/11/2007 17:02:00 0:03:59 17:05:59
3311 02/11/2007 09:24:00 0:00:05 09:24:05
3311 02/11/2007 09:33:00 0:00:05 09:33:05
3380 02/11/2007 10:26:00 0:00:48 10:26:48
3299 02/11/2007 11:17:00 0:01:22 11:18:22
3314 02/11/2007 11:22:00 0:00:30 11:22:30
3016 02/11/2007 11:55:00 0:00:14 11:55:14
3133 02/11/2007 12:01:00 0:03:46 12:04:46
3278 02/11/2007 12:16:00 0:01:06 12:17:06
3258 02/11/2007 12:19:00 0:00:22 12:19:22
3133 02/11/2007 12:37:00 0:00:21 12:37:21
3294 02/11/2007 14:25:00 0:00:04 14:25:04

 

[Bob Phillips]

With your target date in H1

=COUNT(1/FREQUENCY(IF(B2:B8000=H1,IF(A2:A8000<>"",A2:A8000)),IF(B2:B8000=H1,IF(A2:A8000<>"",A2:A8000))))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

 

[Ber]

Thank You so much Bob, maybe I,m slow but what do you mean by Target date in
H1?? Sorry.

 

[Ber]

Bob Sorry I worked out target cell but the formula only works if both ext nos
start a call at exactly the same time on the same date but what I need is if
2 ext nos are on the phone during the same time not necessarly starting or
ending at the same time, e.g someone could start at call at 09:10:00 and stay
on for 15 minutes ending at 09:25:00 and another person could start at
09:12:00 and stay on for 2 minutes ending at 09:14:neither started or ended a
call at the same time but used the phone during the same period of time, this
is what I want to get from this, as I am trying to find out on any date/time
what is the maximum usage of phones, in other words at what period of the day

 

[Glenn]

Bob Sorry I worked out target cell but the formula only works if both ext nos
start a call at exactly the same time on the same date but what I need is if
2 ext nos are on the phone during the same time not necessarly starting or
ending at the same time, e.g someone could start at call at 09:10:00 and stay
on for 15 minutes ending at 09:25:00 and another person could start at
09:12:00 and stay on for 2 minutes ending at 09:14:neither started or ended a
call at the same time but used the phone during the same period of time, this
is what I want to get from this, as I am trying to find out on any date/time
what is the maximum usage of phones, in other words at what period of the day
on any date are most extension numbers used.It's driving me crazy.

There are three intersecting minutes (9:12, 9:13 and 9:14) for the two calls.
Please describe the results you are looking for.

 

[Ber]

Glenn,
Thank you, The sample I gave is only a few of many thousand rows over a year
and there may be lots of cross over of calls heres another example where
A2,A3 and A4 all started and finished calls at different times but were in
the phone at the same time and there are numerous like these on the sheet I
was hoping to get some formula which would give me a figure of say 3 in this
example on 1/11/2007.I need something similar to waht Bob gave me but his
formaula only gave me an answer if the calls started at exactly the same
second which would be unusual. We have 40 different extensions in the
building and we are trying to see maximum usage at any time on any date. To
explain it better if the 40 ext nos were all in use at any particular time
(regardless of the length some of them stayed on as long as they were all
used together)the answer I would get from the formula would be 40.
Ext NO Date (dd/MM/yyyy) Start Time Duration End Time
3224 01/11/2007 08:47:00 0:00:28 08:47:28
3224 01/11/2007 09:25:01 0:05:00 09:30:01
3219 01/11/2007 09:26:00 0:10:00 09:36:00
3336 01/11/2007 09:25:00 0:00:34 09:25:34
3219 01/11/2007 09:51:00 0:00:33 09:51:33
3278 01/11/2007 10:07:00 0:00:25 10:07:25
3016 01/11/2007 10:19:00 0:00:50 10:19:50
3161 01/11/2007 10:32:00 0:00:00 10:32:00
3161 01/11/2007 10:37:00 0:00:00 10:37:00
3311 01/11/2007 11:48:00 0:00:03 11:48:03
3318 01/11/2007 11:54:00 0:00:04 11:54:04
3133 01/11/2007 12:11:00 0:00:37 12:11:37

 

[Glenn]

Glenn,
Thank you, The sample I gave is only a few of many thousand rows over a year
and there may be lots of cross over of calls heres another example where
A2,A3 and A4 all started and finished calls at different times but were in
the phone at the same time and there are numerous like these on the sheet I
was hoping to get some formula which would give me a figure of say 3 in this
example on 1/11/2007.I need something similar to waht Bob gave me but his
formaula only gave me an answer if the calls started at exactly the same
second which would be unusual. We have 40 different extensions in the
building and we are trying to see maximum usage at any time on any date. To
explain it better if the 40 ext nos were all in use at any particular time
(regardless of the length some of them stayed on as long as they were all
used together)the answer I would get from the formula would be 40.
Ext NO Date (dd/MM/yyyy) Start Time Duration End Time
3224 01/11/2007 08:47:00 0:00:28 08:47:28
3224 01/11/2007 09:25:01 0:05:00 09:30:01
3219 01/11/2007 09:26:00 0:10:00 09:36:00
3336 01/11/2007 09:25:00 0:00:34 09:25:34
3219 01/11/2007 09:51:00 0:00:33 09:51:33
3278 01/11/2007 10:07:00 0:00:25 10:07:25
3016 01/11/2007 10:19:00 0:00:50 10:19:50
3161 01/11/2007 10:32:00 0:00:00 10:32:00
3161 01/11/2007 10:37:00 0:00:00 10:37:00
3311 01/11/2007 11:48:00 0:00:03 11:48:03
3318 01/11/2007 11:54:00 0:00:04 11:54:04
3133 01/11/2007 12:11:00 0:00:37 12:11:37

I'm still not completely clear on how you want your final result presented to
you, but this might get you started. Assuming the calls are sorted in order by
date and then start time with headers in row 1, the following formula in row 2
of any free column and copied down will list the number of active calls at any
one time:

=SUMPRODUCT((B2=$B$2:B2)*(C2<=$E$2:E2))+SUMPRODUCT((B2=B2:$B$8000)*(E2>=C2:$C$8000))-1

Substitute the correct number of rows for the 8000 above. This does not include
any checking for calls that go from one day to the next. It counts the number
of calls that (started on the same day) and (started before this call but ended
later) plus all calls that (started on the same day) and (started later than
this call started but before it ended). The minus one at the end is because it
counts the current call twice.

The max number for a date (with the date in H1):

=MAX(IF(B2:B8000=H1,F2:F8000,0))

Again, replace the 8000's with the correct number of rows.

 

[Glenn]

I'm still not completely clear on how you want your final result
presented to you, but this might get you started. Assuming the calls
are sorted in order by date and then start time with headers in row 1,
the following formula in row 2 of any free column and copied down will
list the number of active calls at any one time:

=SUMPRODUCT((B2=$B$2:B2)*(C2<=$E$2:E2))+SUMPRODUCT((B2=B2:$B$8000)*(E2>=C2:$C$8000))-1


Substitute the correct number of rows for the 8000 above. This does not
include any checking for calls that go from one day to the next. It
counts the number of calls that (started on the same day) and (started
before this call but ended later) plus all calls that (started on the
same day) and (started later than this call started but before it
ended). The minus one at the end is because it counts the current call
twice.

The max number for a date (with the date in H1):

=MAX(IF(B2:B8000=H1,F2:F8000,0))

Again, replace the 8000's with the correct number of rows.

And replace the F's with the correct column reference for the previous formula.

 

[Ber]

Glenn, you are a star that is exactly what I was after, tried and tested and
both formula working perfectly. Thank you so much.

 

[Glenn]

Your welcome! Glad I could be of assistance.

Glenn, you are a star that is exactly what I was after, tried and tested and
both formula working perfectly. Thank you so much.