Formula to highlight overlapping dates within a criteria

[MYMunshi]

Hi

I'm struggling to adapt a formula I currently have which helps indicate overlapping dates. I need to adapt this into a conditional formatting technique please.

The table below shows an employee's employment record in a company:


[TABLE="width: 489"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Record[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD] Pattern[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]17/08/2016[/TD]
[TD="align: right"]06/07/2017[/TD]
[TD] PT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]01/04/2015[/TD]
[TD="align: right"]18/08/2016[/TD]
[TD] PT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]03/12/2007[/TD]
[TD="align: right"]31/03/2015[/TD]
[TD] FT[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]22/12/2006[/TD]
[TD="align: right"]02/12/2007[/TD]
[TD] PT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20/12/2006[/TD]
[TD="align: right"]02/02/2007[/TD]
[TD] FT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17/07/2003[/TD]
[TD="align: right"]24/12/2006[/TD]
[TD] FT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11/10/2000[/TD]
[TD="align: right"]16/07/2003[/TD]
[TD] FT[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]02/09/1998[/TD]
[TD="align: right"]10/10/2000[/TD]
[TD] FT[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]01/02/1997[/TD]
[TD="align: right"]31/08/1998[/TD]
[TD] FT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10/06/1996[/TD]
[TD="align: right"]31/08/1998[/TD]
[TD] PT[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]


As you can see, there are some date bands which overlap against others within the range.

The formula I am using in column E is (for the purposes of Record 10): =SUMPRODUCT(($C3<=$D$3:$D$12)*($D3>=$C$3:$C$12))>1

The big problem here is, an overlap can only be regarded as an "overlap" under two separate conditions:

1. a FT (full time) record overlapping a FT record
2. a PT (part time) record overlapping a FT record

Any number of PT records can overlap each other as many times as they want, but the above two conditions need to be met in order for it to be regarded as a true overlap.

So from the data in the table above:

• Record 10 and 8 are PT jobs the employee took. They are not true overlaps as they are both PT and shouldn't be marked TRUE.

• Record 6 (a PT post) overlaps Record 9 (a FT post). In turn, Record 9 overlaps Record 5 (a FT post) by 4 days. Both of these cases are true overlaps.
• Record 2 is a PT post which truly overlaps Record 1 WT.

I need a formula please to conditional format the range C3:D12 so that Excel can highlight the overlapping date bands.

Any suggestions please? A solution to this would seriously alleviate a lot of stress and the need for drawing out timelines!

Thank you so much in advance for any help