Different color of same date range in a column Exc

I have leave manager i want to color different if two or more dates are same i am using this formula

=OR(SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S1]"))*(B6<=INDIRECT("Table1[E1]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S2]"))*(B6<=INDIRECT("Table1[E2]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S3]"))*(B6<=INDIRECT("Table1[E3]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S4]"))*(B6<=INDIRECT("Table1[E4]"))) ,SUMPRODUCT((B6<>"")* (B6>=INDIRECT("Table1[S5]"))*(B6<=INDIRECT("Table1[E5]"))))

The formula you want with all the columns is:

=1 < SUM(COUNTIFS(INDIRECT("Table1[Start]"),"<=" & B6,INDIRECT("Table1[End]"),">="& B6),COUNTIFS(INDIRECT("Table1[SD1]"),"<=" & B6,INDIRECT("Table1[ED1]"),">="& B6),...),0)

Where the ... is more countifs for the number of groupings

But that is not using Excel's functions to their potential. As I said in my comments, fewer columns allows for the same functionality with shorter and more accurate formulas.

The formula in the count column is:

=IF(Z5<>"",COUNTIF($Z$5:$Z5,Z5),"")

You could put a conditional formatting on this column to highlight when they have reached the 8 max:

=AC5 >= 8

Then the conditional formatting in the calendars would be:

For the overlap:

= 1 < COUNTIFS(INDIRECT("Table3[Start]"),"<=" & B6,INDIRECT("Table3[End]"),">=" & B6)

For the employee:

=SUMPRODUCT((INDIRECT("Table3[Names]")="abc")*(INDIRECT("Table3[Start]")<=B6)*(INDIRECT("Table3[End]")>=B6))

Again Changing the name of the employee.

With the data formatted as a table the reference will grow to accommodate new entries. Since the formula requires the employee name it does not matter if there is 1 or 100 entries for that employee it will always be the same color.

Also this allow one to use the filters that are inherent in the table, to sort and filter on employees and dates.