Duplicate/overlapping data

[Jazzman]

Hi!


I have these columns:

A: Date

B: start hrs

C: end hrs

D: Name


I have a working formula that check's if there are duplicate start/end hours at a same date (if that's the case the formula returns the value 1 in column E).


Now! my problem is that I can't figure out a formula that tells me if th name in column D appears more than 1 time at the same date, if so is the case I need a formula that returns the value 1 in column F.


I Sum columns E and F in column G. Then if value in column G <2 the result is "Ok" else "Not ok".


Duplicate example: (because the date is the same for Jack and start-end overlaps).

01.05.2013 10:00 - 13:00 Jack

01.05.2013 11:00 - 14:00 Jack.


Not duplicate: (because date is not the same)

01.05.2013 10:00 - 13:00 Jack

02.05.2013 11:00 - 14:00 Jack

 

[Faseeh]

Hi Jazzman,


You can try this:

=IF(AND(B3>=B2,B3<=C2),IF(A3&D3=A2&D2,1,0))

with your data in A1:E3. But i have a question:


What if there are more then one occasion when there is a duplicate data, and that dates are grouped or scattered (data is sorted for date or not).


Regards,

 

[Jazzman]

Hi!


It didn't work. Let's say that Jack can have up to 5-7 entries on the same date, in this case 01.05.2013 and then again the same at 02.05.2013 and 03.05.2013 and this will go on 5 days a week for year the 2013, and 2014 and so on.


And! I have 10 employees, and they can all have from 1 to 7 entries on at the same date (5 times a week).


This formula checks if there are overlapping in start-end hrs. And it return the value 1 in column E for overlapping time periods.

=SUMPRODUCT(--((A1+C1<=$A$1:$A$30000+$B$1:$B$30000)+(A1+B1>=$A$1:$A$30000+$C$1:$C$30000)=0))<>1


Now I need a formula (in column F) that tells me how many entries there is for Jack on 01.05.2013. And how many entries on 02.05.2013 and so on.


A normal workday would look like thiscolumns A, B, C, D)

row 2: 01.05.2013 07:00-12:00 Will

row 3: 01.05.2013 07:30-11:00 Jack

row 4: 01.05.2013 11:30-13:00 Jack

row 5: 01.05.2013 11:30-15:00 Will

row 6: 01.05.2013 12:45-16:00 Jack < this is a duplicate/overlap with Jack on row 4.

row 7: 02.05.2013 07:30-11:00 Jack < this in not a overlap with row 3, because it is not the same date.


In this example I need a formula to return the value 1 in cell F6, the other formula returns the value 1 in cell E6. And in G6 I have the formula =SUM(E6,F6), in this case G6 = 2 and it tells me that this is not Ok, and with Conditional Formatting I can highlight row 6 with a different color.


Regards,

 

[NARAYANK991]

Hi ,


Will this link help ?


http://www.linkedin.com/groups/Ideas-shortening-massive-formula-that-3843467.S.216410614?view=&srchtype=discussedNews&gid=3843467&item=216410614&type=member&trk=eml-anet_dig-b_pd-ttl-cn&ut=2b8Prs696ah5E1


Narayan

 

[SirJB7]

Hi, Jazzman!

Already read this?

http://chandoo.org/wp/2011/03/09/ec1-machine-scheduling-in-excel/

http://chandoo.org/forums/topic/find-if-2-sets-of-times-overlap-calculate-time-overlapped-even-through-midnight

Regards!