Finding overlapping time instances

[shauncrom]

Hi

I am trying to work out how to calculate the number of overlapping instances based on ranges of time.
Basically I have a number of desks that connect to jobs and then disconnect. What I am trying to do is work out the capacity load on the desks- to see how many desks are connected to different jobs at the same time.
I have tried one possible solution in row F but this just seems to return a true vale all the time.
Some desks do not connect to a job at all- and sometimes the data is missing from column b or c- I may have to filter these out.

Any suggestions are gratefully received

regards

[loginjmor]

Hi -

I think the problem is you just forgot to have your SUMPRODUCT check that the Connected Desk number is included in your comparison. I have added that to the beginning of the SUMPRODUCT as follows:

=IF(B2=0,"NO",SUMPRODUCT((A2=$A$2:$A$7511)*(B2<=$C$2:$C$7511)*(C2>=$B$2:$B$7511))>1)

Copy and paste this into F2 and then copy down.

It appears to work. I checked Row 16 that does appear to have an overlap.

Hope this helps.

[shauncrom]

LogiJmor- Thanks for the advice
Just one query This seems to show true for only overlapping instances of the value of column A

So I adjusted the first part of the arguement to look at the day instead as I want to see any overlapping time regardless of the desk_ probably didnt make this clear.
So the entire list will have all days of the week- just wondering why this doesn't work

I have attached the file with two highlighted rows that should show false

Is it just a column sequence issue?

Many thanks for your help so far

[loginjmor]

Hi -

I was looking at your formula, and the problem is it counts some of your blank cell entries as matches. So, I just added a component in your SUMPRODUCT to verify it's only comparing rows that actually have data in them. The formula looks like:

=IF(B2=0,"NO",SUMPRODUCT((D2=$D$2:$D$212)*(B2<=$C$2:$C$212)*(C2>=$B$2:$B$212)*($B$2:$B$212>0))>1)

If you get rid of the ">1" at the end of the formula, you can see how many times you have overlaps of that particular entry. 1 means no overlap. 2 means 1 overlap. 3 means 2, etc. That's not what you asked for, but that's how I tracked down the problem. So, you can copy and paste the above formula into cell F2 and copy down and you should get the correct result.

Hope this gets it for you.

[shauncrom]

Hi

Thanks- apologies for not stating clearly in the first place- I will give it ago.

Many thanks for taking the time to look at this.

Best regards

Shaun

[shauncrom]

LoginJmor

Just to let you know- that did the trick

Again- many thanks

Shaun

[loginjmor]

Great! No problem. Don't forget to mark your post SOLVED per the instructions at the bottom of my very first post on this thread.