software

Hi,

I have a long list of events in excel each with a start and end date/time. I
need to work out the maximum number of concurrent events at any one time in
the list. Any ideas on how to do it?

Dave

Assume...
A B C
1 Event Start Date End Date
2 Circus 01/01/2006 01/05/2006
3 Play 01/01/2006 01/03/2006
4 Auction 01/02/2006 01/04/2006
5 Debate 01/03/2006 01/03/2006
6 Game 01/04/2006 01/05/2006

In E1, put quot;Datesquot;
In F1, put quot;Eventsquot;

In E2 down to E???, put the dates you want to check on.
In F2 put the following ARRAY formula. That means, when you have completed
the formula, instead of hitting ENTER, you hit CTRL-SHIFT-ENTER. The { and }
will appear at the beginning and end of the formula. Copy this formula down
to the end of your 'Dates' list.

Formula in F2:
=SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0))

after hitting Ctrl-Shift-Enter will look like...
{=SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0)) }Your worksheet should now look something like...

A B C D E F
1 Event Start Date End Date Dates Events
2 Circus 01/01/2006 01/05/2006 01/01/2006 2
3 Play 01/01/2006 01/03/2006 01/02/2006 3
4 Auction 01/02/2006 01/04/2006 01/03/2006 4
5 Debate 01/03/2006 01/03/2006 01/04/2006 3
6 Game 01/04/2006 01/05/2006 01/05/2006 2
7 01/06/2006 0
8 01/07/2006 0
9 01/08/2006 0

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.quot;Dave at tchquot; wrote:

gt; Hi,
gt;
gt; I have a long list of events in excel each with a start and end date/time. I
gt; need to work out the maximum number of concurrent events at any one time in
gt; the list. Any ideas on how to do it?
gt;
gt; Dave

Thanks for your help Gary - we may be very nearly there.

My list looks like this

startdatetimeEndtime
01/10/2005 8:50:1201/10/2005 8:50:16
01/10/2005 10:30:5001/10/2005 10:30:57
01/10/2005 10:38:1101/10/2005 10:38:19
01/10/2005 10:39:4101/10/2005 10:39:50
01/10/2005 10:42:3701/10/2005 10:48:30
01/10/2005 10:48:0201/10/2005 10:48:05
01/10/2005 12:01:5401/10/2005 12:02:36
01/10/2005 13:20:4901/10/2005 13:20:52
01/10/2005 14:16:0501/10/2005 14:16:33
01/10/2005 17:38:2301/10/2005 17:45:20
01/10/2005 18:58:4201/10/2005 18:58:52
01/10/2005 22:23:0401/10/2005 22:23:04

I don't think I can add every second of a month in the next column and then
use your formula (64k lines is not enough). Any other ides or should I use
your idea but work in Access instead?

Thank you very much for your help

Dave

quot;Gary L Brownquot; wrote:

gt; Assume...
gt; A B C
gt; 1 Event Start Date End Date
gt; 2 Circus 01/01/2006 01/05/2006
gt; 3 Play 01/01/2006 01/03/2006
gt; 4 Auction 01/02/2006 01/04/2006
gt; 5 Debate 01/03/2006 01/03/2006
gt; 6 Game 01/04/2006 01/05/2006
gt;
gt; In E1, put quot;Datesquot;
gt; In F1, put quot;Eventsquot;
gt;
gt; In E2 down to E???, put the dates you want to check on.
gt; In F2 put the following ARRAY formula. That means, when you have completed
gt; the formula, instead of hitting ENTER, you hit CTRL-SHIFT-ENTER. The { and }
gt; will appear at the beginning and end of the formula. Copy this formula down
gt; to the end of your 'Dates' list.
gt;
gt; Formula in F2:
gt; =SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0))
gt;
gt; after hitting Ctrl-Shift-Enter will look like...
gt; {=SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0)) }
gt;
gt;
gt; Your worksheet should now look something like...
gt;
gt; A B C D E F
gt; 1 Event Start Date End Date Dates Events
gt; 2 Circus 01/01/2006 01/05/2006 01/01/2006 2
gt; 3 Play 01/01/2006 01/03/2006 01/02/2006 3
gt; 4 Auction 01/02/2006 01/04/2006 01/03/2006 4
gt; 5 Debate 01/03/2006 01/03/2006 01/04/2006 3
gt; 6 Game 01/04/2006 01/05/2006 01/05/2006 2
gt; 7 01/06/2006 0
gt; 8 01/07/2006 0
gt; 9 01/08/2006 0
gt;
gt; HTH,
gt; --
gt; Gary Brown
gt;
gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; Post Helpfull to you?''.
gt;
gt;
gt; quot;Dave at tchquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; I have a long list of events in excel each with a start and end date/time. I
gt; gt; need to work out the maximum number of concurrent events at any one time in
gt; gt; the list. Any ideas on how to do it?
gt; gt;
gt; gt; Dave

Sorry, when you wrote of 'events', I thought you were in Property Management.
I can't think of another way to do this. Why don't you try re-posting this
to see if someone else can come up with a better solution?
Don't just reply because the posting will stay way down here in 1/12/2006
instead of at the top in 1/13/2006.
HTH and Good Luck.
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.quot;Dave at tchquot; wrote:

gt; Thanks for your help Gary - we may be very nearly there.
gt;
gt; My list looks like this
gt;
gt; startdatetimeEndtime
gt; 01/10/2005 8:50:1201/10/2005 8:50:16
gt; 01/10/2005 10:30:5001/10/2005 10:30:57
gt; 01/10/2005 10:38:1101/10/2005 10:38:19
gt; 01/10/2005 10:39:4101/10/2005 10:39:50
gt; 01/10/2005 10:42:3701/10/2005 10:48:30
gt; 01/10/2005 10:48:0201/10/2005 10:48:05
gt; 01/10/2005 12:01:5401/10/2005 12:02:36
gt; 01/10/2005 13:20:4901/10/2005 13:20:52
gt; 01/10/2005 14:16:0501/10/2005 14:16:33
gt; 01/10/2005 17:38:2301/10/2005 17:45:20
gt; 01/10/2005 18:58:4201/10/2005 18:58:52
gt; 01/10/2005 22:23:0401/10/2005 22:23:04
gt;
gt; I don't think I can add every second of a month in the next column and then
gt; use your formula (64k lines is not enough). Any other ides or should I use
gt; your idea but work in Access instead?
gt;
gt; Thank you very much for your help
gt;
gt; Dave
gt;
gt; quot;Gary L Brownquot; wrote:
gt;
gt; gt; Assume...
gt; gt; A B C
gt; gt; 1 Event Start Date End Date
gt; gt; 2 Circus 01/01/2006 01/05/2006
gt; gt; 3 Play 01/01/2006 01/03/2006
gt; gt; 4 Auction 01/02/2006 01/04/2006
gt; gt; 5 Debate 01/03/2006 01/03/2006
gt; gt; 6 Game 01/04/2006 01/05/2006
gt; gt;
gt; gt; In E1, put quot;Datesquot;
gt; gt; In F1, put quot;Eventsquot;
gt; gt;
gt; gt; In E2 down to E???, put the dates you want to check on.
gt; gt; In F2 put the following ARRAY formula. That means, when you have completed
gt; gt; the formula, instead of hitting ENTER, you hit CTRL-SHIFT-ENTER. The { and }
gt; gt; will appear at the beginning and end of the formula. Copy this formula down
gt; gt; to the end of your 'Dates' list.
gt; gt;
gt; gt; Formula in F2:
gt; gt; =SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0))
gt; gt;
gt; gt; after hitting Ctrl-Shift-Enter will look like...
gt; gt; {=SUM(IF($B$2:$B$6lt;=E2,1,0)*IF($C$2:$C$6gt;=E2,1,0)) }
gt; gt;
gt; gt;
gt; gt; Your worksheet should now look something like...
gt; gt;
gt; gt; A B C D E F
gt; gt; 1 Event Start Date End Date Dates Events
gt; gt; 2 Circus 01/01/2006 01/05/2006 01/01/2006 2
gt; gt; 3 Play 01/01/2006 01/03/2006 01/02/2006 3
gt; gt; 4 Auction 01/02/2006 01/04/2006 01/03/2006 4
gt; gt; 5 Debate 01/03/2006 01/03/2006 01/04/2006 3
gt; gt; 6 Game 01/04/2006 01/05/2006 01/05/2006 2
gt; gt; 7 01/06/2006 0
gt; gt; 8 01/07/2006 0
gt; gt; 9 01/08/2006 0
gt; gt;
gt; gt; HTH,
gt; gt; --
gt; gt; Gary Brown
gt; gt;
gt; gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; gt; Post Helpfull to you?''.
gt; gt;
gt; gt;
gt; gt; quot;Dave at tchquot; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I have a long list of events in excel each with a start and end date/time. I
gt; gt; gt; need to work out the maximum number of concurrent events at any one time in
gt; gt; gt; the list. Any ideas on how to do it?
gt; gt; gt;
gt; gt; gt; Dave