Overlapping dates

[heatherrink]

Hi there, please can you assist with my question - I have 5 sets of start and end dates each for 5 therapies. I need to determine if any of these 5 date periods overlap. I have inserted an example below, (in this case the person is only on 2 therapies, but I dont think it matters to my overall question). Do you have any idea how I can accomplish this in a formula? Thanks very much.

1</SPAN></SPAN>	2</SPAN></SPAN>	3</SPAN></SPAN>	4</SPAN></SPAN>	5</SPAN></SPAN>
Premix First Date</SPAN></SPAN>	Premix Last Date</SPAN></SPAN>	Non-Premix First Date</SPAN></SPAN>	Non-Premix Last Date</SPAN></SPAN>	Fast Acting First Date</SPAN></SPAN>	Fast Acting Last Date</SPAN></SPAN>	Orals First Date</SPAN></SPAN>	Orals Last Date</SPAN></SPAN>	GDS First Date</SPAN></SPAN>	GDS Last Date</SPAN></SPAN>
.</SPAN></SPAN>	.</SPAN></SPAN>	14-Sep-05</SPAN></SPAN>	09-Oct-07</SPAN></SPAN>	03-Nov-99</SPAN></SPAN>	15-Jul-05</SPAN></SPAN>	.</SPAN></SPAN>	.</SPAN></SPAN>	.</SPAN></SPAN>	.</SPAN></SPAN>

<TBODY>
</TBODY>

 

[Snakehips]

heatherrink,

Not sure if there is a more elegant solution so perhaps something like this.......

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Premix First Date	Premix Last Date	Non-Premix First Date	Non-Premix Last Date	Fast Acting First Date	Fast Acting Last Date	Orals First Date	Orals Last Date	GDS First Date	GDS Last Date
2			14/09/2005	09/10/2007	03/11/1999	15/07/2005					FALSE
3			15/07/2005	09/10/2007	03/11/1999	15/07/2005					TRUE
Sheet1

Cell Formulas
Range	Formula
K2	=OR(IFERROR((RANK(A2,A2:J2)-RANK(B2,A2:J2))<>1,FALSE),IFERROR((RANK(C2,A2:J2)-RANK(D2,A2:J2))<>1,FALSE),IFERROR((RANK(E2,A2:J2)-RANK(F2,A2:J2))<>1,FALSE),IFERROR((RANK(G2,A2:J2)-RANK(H2,A2:J2))<>1,FALSE),IFERROR((RANK(I2,A2:J2)-RANK(J2,A2:J2))<>1,FALSE),SUMPRODUCT(--(COUNTIF(A2:J2,A2:J2)>1)))

It should, hopefully, return TRUE if there is any overlap of the 5 sets of dates.

Hope that helps.