Formula to highlight overlapping dates within a criteria

MYMunshi

New Member
Joined
Apr 16, 2016
Messages
16
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:


ABCDEF
1
2RecordStart DateEnd Date Pattern
31017/08/201606/07/2017 PTTRUE
4801/04/201518/08/2016 PTTRUE
5703/12/200731/03/2015 FTFALSE
6622/12/200602/12/2007 PTTRUE
7920/12/200602/02/2007 FTTRUE
8517/07/200324/12/2006 FTTRUE
9411/10/200016/07/2003 FTFALSE
10302/09/199810/10/2000 FTFALSE
11101/02/199731/08/1998 FTTRUE
12210/06/199631/08/1998 PTTRUE

<tbody>
</tbody>


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 :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top