How to check overlapping days and times

Hi Bill,

I have sorted out how to add the correct date and time together using the above.

My Start Date + Start Time is in Cells "CJ17:CJ216"

My End Date + End Time is in Cells "CN17:CN216"

The formula for working out is... {=IF(CU17="Yes",SUM((CJ17<CN$17:CN$216)*(CN17<CJ$17:CJ$216)),"")}

But for some reason I am getting the following... 163 from the above formulas.

What I want is for it to do the following...

If 1 Programme, do not overlap, then do nothing.

If 2 Programmes, do nothing, OK with 2 overlap.

If 3 Programmes, do nothing, OK with 3 overlap.

If 4 or more Programmes, "Error, Too Many Overlapping Programmes"

I am not sure why it is not working as I have the column for each of the Items that you have said in your last message.

Can you see by looking at the information I've said above about where the dates are and the formula for working out the overlaps?

Thank you,

Neil

I don't think you need the formulas in CL to CP for the purpose of determining the number of overlaps:

In CL (Start Date+Time) put =CH17+CI17

In CM (End Date+Time) put =CH17+CJ17+IF(CJ17<CI17,1,0)

In CN (Number of overlaps) put =SUM((CL17<CM$17:CM$216)*(CM17<CL$17:CL$216))

  and enter the last with Ctrl+Shift+Enter so that it is an array formula

Fill these 3 formulas down to row 216

Hi Bill,

Thank you for your reply.

I have a problem to add the date to the start time and end time.

When I first created the worksheet I don't really use dates with times.

I only really used the date to sort the list into date order.

I have a lookup table with the dates for the week, This is a copy of how the lookup table looks...

Day                    Date

Saturday            11/05/2013

Sunday              12/05/2013

Monday            13/05/2013

Tuesday            14/05/2013

Wednesday      15/05/2013

Thursday          16/05/2013

Friday               17/05/2013

Saturday          18/05/2013

I then enter the day, and then the following formula I use to insert the date in my table

=IF(B17="","",$L$16+IF(E17="Sat",0,IF(E17="Sun",1,IF(E17="Mon",2,IF(E17="Tue",3,IF(E17="Wed",4,IF(E17="Thu",5,IF(E17="Fri",6,IF(E17="Next",7,0)))))))))

I use the following formula to correct the end time if goes over midnight

=IF(H17<>"",IF(HOUR(X17)<12,X17+(12*60)+MINUTE(X17),X17)," ")

So I am not sure how I would add the date to both start and end times.

To add the date to the start time, I would guess that the formula would be...

date+start time

but how would I add the date to the end time?

Just to let you know I've copied the date, start time and end time to a new area of the sheet just to work out if the times overlap, here are the cell ref for this new area...

Date = CH17:CH216

Start Time = CI17:CI216

End Time = CJ17:CJ216

I have also set the following to show working out and information I may need to know...

Day of Week = CG17:CG216

Code for information = CK17:CK216

Same Date = CL17:CL216 Formula: =IF(CH17<>"",IF(CH17=CH16,"Yes","No"),"")

Same Start Time = CM17CM216 Formula =IF(CH17<>"",IF(CI17=CI16,"Yes","No"),"")

Same End Time = CN17:CN216 Formula =IF(CH17<>"",IF(CJ17=CJ16,"Yes","No"),"")

Start Time Before End Time = CO17:CO216 Formula =IF(CH17<>"",IF(CI17<CJ16,"Yes","No"),"")

Need to Check for Overlap = CP17:CP216 Formula =IF(CH17<>"",IF(OR(CK17="Y",CK17="A"),"Yes","No"),"")

Can I use a formula to check for overlaps with the details within this new table.

Can add new items to the table, i.e. can add a column for end date, then a column for start date + start time, then a column end date + end time.

Then use the formula you gave in your first reply.

Neil

I think it would be easier if you held the start and end times as date+time values.

That way there would be no correction needed for recordings that go over midnight.

I will assume that the values in B and C are changed to be such date+time values

Times overlap if the Start of A is less than the End of B AND the Start of B is less than the End of A

So the number of recordings that overlap with the one in row 2 is:

=SUM((B2<C$2:C$1000)*(C2<B$2:B$1000))

entered using Ctrl+Shift+Enter. 

I have assumed there are not more than 1000 rows - adjust as necessary.