Formula needs for duplicate values with overlapping date ranges

[sabrinamb]

Hi,

I currently have a list of colleagues who have hired cars. I have their collegaue numbers in column A. I then have the start date of the car hire in column B and the end date of the car hire in column C. I need to highlight colleagues who have had more then 1 car hire within the same date range. So in effect i need the formula to first find duplicates in column A and then compare the dates to see whether any overlap and highlight those that do in a colour, so that i can investigate these.

I've tried to use conditional formatting but i dont know how to include the date range comparison into this.

Any help would be much appreciated.

Thanks
Sabrina

Example: 745CVB and 852TGB should be highlighted as they have overlapping dates

Col A Col B Col C
745CVB 01/04/2010 02/08/2010
745CVB 03/08/2010 15/09/2013
745CVB 07/05/2013 06/05/2014
985MNB 02/06/2011 25/05/2013
985MNB 27/05/2013 06/05/2014
852TGB 05/02/2012 12/12/2013
852TGB 01/12/2013 01/01/2014

 

[skorpionkz]

2 questions.

Does your database sorted by column A?
Are you need to highlight only rows which are overlapping or all rows for this colleague?</SPAN>

 

[sabrinamb]

Thank you for your reply.

Yes can sort by column A and yes it would need to highlight all rows for the colleague

 

[skorpionkz]

try this

Excel 2010
	A	B	C	D	E
1	Number	Start	End
2	745CVB	01/04/2010	02/08/2010		Highlight
3	745CVB	03/08/2010	15/09/2013	Overlapping	Highlight
4	745CVB	07/05/2013	06/05/2014	Overlapping	Highlight
5	985MNB	02/06/2011	25/05/2013
6	985MNB	27/05/2013	06/05/2014
7	852TGB	05/02/2012	12/12/2013	Overlapping	Highlight
8	852TGB	01/12/2013	01/01/2014	Overlapping	Highlight
Sheet1

Cell Formulas
Range	Formula
D2	=IF(SUMPRODUCT(--($A$2:$A$8=A2)*--($B$2:$B$8)*--($C$2:$C$8>B2))>1,"Overlapping","")
E2	=IF(COUNTIFS(A2:A8,A2,D2:D8,"Overlapping")>=1,"Highlight","")

 

[sabrinamb]

Perfect!

Thank you for all your help

 

[skorpionkz]

You are welcome!