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
[TABLE="width: 1007"]
<colgroup><col><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Yunus[/TD]
[TD]Dan[/TD]
[TD]James[/TD]
[TD]John[/TD]
[TD]Steve[/TD]
[TD]Danny[/TD]
[TD]Ian[/TD]
[TD]Adam[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]02-Mar[/TD]
[TD]S[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DAYS
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]03-Mar[/TD]
[TD]S[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD]DAYS
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]04-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]05-Mar[/TD]
[TD]T[/TD]
[TD]23:00 BTA Refresher course[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]06-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]07-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]WTD[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]08-Mar[/TD]
[TD]F[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]WTD[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]09-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[TD]8h DCS Testing R19 nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[TD]8h DCS Testing R19 nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]12-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]13-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]14-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]15-Mar[/TD]
[TD]F[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]16-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]17-Mar[/TD]
[TD]S[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]18-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]19-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]20-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]21-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]22-Mar[/TD]
[TD]F[/TD]
[TD]shift change[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]WTD[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
</tbody>[/TABLE]
=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
[TABLE="width: 1007"]
<colgroup><col><col><col span="7"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Yunus[/TD]
[TD]Dan[/TD]
[TD]James[/TD]
[TD]John[/TD]
[TD]Steve[/TD]
[TD]Danny[/TD]
[TD]Ian[/TD]
[TD]Adam[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]02-Mar[/TD]
[TD]S[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DAYS
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]03-Mar[/TD]
[TD]S[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD]DAYS
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]04-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]05-Mar[/TD]
[TD]T[/TD]
[TD]23:00 BTA Refresher course[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]06-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]07-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]WTD[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]08-Mar[/TD]
[TD]F[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]WTD[/TD]
[TD]DCS testing R19 Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]09-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[TD]8h DCS Testing R19 nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Days[/TD]
[TD]8h DCS Testing R19 nights[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]12-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]13-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]14-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]15-Mar[/TD]
[TD]F[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]16-Mar[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]17-Mar[/TD]
[TD]S[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD] [/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]18-Mar[/TD]
[TD]M[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]NCSC Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]19-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Days[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]20-Mar[/TD]
[TD]W[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]21-Mar[/TD]
[TD]T[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
[TR]
[TD]22-Mar[/TD]
[TD]F[/TD]
[TD]shift change[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]WTD[/TD]
[TD]WTD[/TD]
[TD]Nights[/TD]
[TD]Nights[/TD]
[TD]Leave[/TD]
[TD]Nights[/TD]
[/TR]
</tbody>[/TABLE]