Time overlap in excel or access

[prldoyle]

Hello,

I have a list in an access query (but happy to export to Excel for the needed result) of start time and end time (about 4000 of them). These are start time and end time of phone calls within a company. i need to find out the maxium concurrent calls at any one time.

The list
CALLSTARTTIMEINGRESSGWACCESS CALLENDTIME
2009-5-29 4:04 PM 2009-5-29 4:04 PM
2009-6-1 3:01 PM 2009-6-1 3:01 PM
2009-6-9 11:54 AM 2009-6-9 11:57 AM
2009-6-9 12:07 PM 2009-6-9 12:08 PM
2009-6-9 12:08 PM 2009-6-9 12:08 PM
2009-6-9 12:22 PM 2009-6-9 12:22 PM
2009-6-9 12:24 PM 2009-6-9 12:24 PM
2009-6-9 11:54 AM 2009-6-9 11:55 AM
2009-6-10 12:29 PM 2009-6-10 12:30 PM
2009-6-10 12:44 PM 2009-6-10 12:49 PM

Any help would be great.
cheers
Pete

 

[SkipVought]

Pete,

Would you care to explain clearly what you mean by, "...the maxium concurrent calls at any one time."

"At any one time" is an instant, something like 2009-5-29 4:05 PM, unless I'm missing something.

Explain the desired result, based on your posted example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[prldoyle]

Sorry, i need to know the maxium number of current calls for the whole list. (so over the who list there are 30 entries (30 calls) happen at the same time)

I hope i'm explaining my self clearly. If not please let me know and i will try again.

Cheers
Pete

 

[SkipVought]

I don't see 30 call in your example.

YOU know what you want, but it is not apparent to me what you want.

Please be clear, concise and complete.

Please use your example and explain in excruciating detail how you use this data to arrive at your answer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[prldoyle]

sorry 30 was just an examle. The data in my first post is just a subet of a much large set of data. at a guess there will be about 4000 entries.

Each entry is a single phone call, showing the start time/date and end time/date. This list comes stright form the PBX call detail server.

I currently have this data in a query with in access but willing to export to excel if it makes getting the final result, easier.

These 4000 calls will be over a few months. I need to know over the total period what was the most concurrent calls.

I hope this makes more sense.

Cheers
Pete

 

[SkipVought]

That is NOT what I asked. I don't care about those 4000. I only care about your example!

Please use your example, and given ONLY that data, explain in detail how you would arrive at an answer.

PLEASE!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[prldoyle]

ok,

In the example there is two concurrent calls

2009-5-29 4:04 PM 2009-5-29 4:04 PM
2009-6-1 3:01 PM 2009-6-1 3:01 PM
2009-6-9 11:54 AM 2009-6-9 11:57 AM
2009-6-9 12:07 PM 2009-6-9 12:08 PM
2009-6-9 12:08 PM 2009-6-9 12:08 PM
2009-6-9 12:22 PM 2009-6-9 12:22 PM
2009-6-9 12:24 PM 2009-6-9 12:24 PM
2009-6-9 11:54 AM 2009-6-9 11:55 AM
2009-6-10 12:29 PM 2009-6-10 12:30 PM
2009-6-10 12:44 PM 2009-6-10 12:49 PM

As these both overlap the same time. the rest of the calls made were at all seperate times and did not over lap. so for the example list, the result i am after would be 2.

 

[SkipVought]

Thank you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's a formula using your example...
[tt]
C2: =SUMPRODUCT(--(A2<=$B$2:$B$11)*(B2>=$A$2:$A$11))
[/tt]
adjust the range accordingly.

Use the MAX function to return the highest number of occurrences.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[prldoyle]

Excellent, thank you