Excel - Conditional Formatting Formulae

[yunus786]

I am using this formula in Conditional Format:
=COUNTA(D8:D21)-COUNTIF(D8:D21,"Sick")-COUNTIF(D8:D21,"Shift Change")-COUNTIF(D8:D20,"WTD")-COUNTIF(D8:D20,"Bank Holiday")-COUNTIF(D8:D20,"*Leave*")-COUNTIF(D8:D21,"OFF")-COUNTIF(D8:D21,"Not Available")-COUNTIF(D8:D21,"*LEAVE")>13
It highlights Days in red if you have worked more than 13days in a 14 day period. The problem is it highlights the first set of days in the gorup not the last set e.g. In the example below, john has worked from 4th March all the way through to the 21st March without taking a Day Off. The formula highlights the days between 4th and 9th as Days overworked. I would prefer it highlighted after the 13 consecutive day period has expired hence it should highlight from 17th to 21st as the day overworked (4th March + 12 days=13 consective days). This way it immendiately flags the day prohibited rather than scrolling back 14 days to check if it is prohibited

		Yunus	Dan	James	John	Steve	Danny	Ian	Adam	David
02-Mar	S	Days	Days	Nights			DAYS			Days
03-Mar	S	Days	Days	Nights			DAYS			Days
04-Mar	M	Nights	Days	Nights	NCSC Days	Nights	WTD	Nights	Nights	Days
05-Mar	T	23:00 BTA Refresher course	Days	Nights	NCSC Days	Nights	Days	Nights	Nights	Days
06-Mar	W	Nights	Days	Nights	DCS testing R19 Nights	Nights	Days	Nights	Nights	Days
07-Mar	T	Nights	Days	WTD	DCS testing R19 Nights	Nights	Days	Nights	Nights	Days
08-Mar	F	Nights	Days	WTD	DCS testing R19 Nights	Nights	Days	Nights	Nights	Days
09-Mar	S			Days	8h DCS Testing R19 nights
10-Mar	S			Days	8h DCS Testing R19 nights			Nights
11-Mar	M	Nights	Nights	Days	NCSC Days	Nights	Days	Nights	Nights	Nights
12-Mar	T	Nights	Nights	Nights	NCSC Days	Nights	Days	Nights	Nights	Nights
13-Mar	W	Nights	Nights	Nights	Nights	Nights	Days	Nights	Nights	Nights
14-Mar	T	Nights	Nights	Nights	Nights	Nights	Days	Nights	Nights	Nights
15-Mar	F	Nights	Nights	Nights	Nights	Nights	Days	Nights	Nights	Nights
16-Mar	S			Nights	Days	Nights	Nights	Nights	Days	Days
17-Mar	S	Nights	Nights	Nights	Days	Nights	Nights	Nights		Nights
18-Mar	M	Nights	Nights	WTD	NCSC Days	Nights	Nights	Nights	Leave	Nights
19-Mar	T	Nights	Nights	Nights	Days	Nights	Nights	Nights	Leave	Nights
20-Mar	W	Nights	Nights	Nights	Nights	Nights	Nights	Nights	Leave	Nights
21-Mar	T	Nights	Nights	Nights	Nights	Nights	Nights	Nights	Leave	Nights
22-Mar	F	shift change	Nights	Nights	WTD	WTD	Nights	Nights	Leave	Nights

<colgroup><col><col><col span="7"><col><col></colgroup><tbody>
</tbody>