Highlight days in a row from vertical lists

[Johana H]

Hi there,
I have a spreadsheet that includes the dates and Number of days the employees have been taking sick leave in the last year. The information is shown as per the following table:

[TABLE="width: 365"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Full Name[/TD]
[TD]Leave Start Date[/TD]
[TD]Leave End Date[/TD]
[TD]Units Taken[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]8/09/2016[/TD]
[TD="align: right"]8/09/2016[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]7/12/2016[/TD]
[TD="align: right"]7/12/2016[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]8/12/2016[/TD]
[TD="align: right"]8/12/2016[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD="align: right"]10/12/2016[/TD]
[TD="align: right"]10/12/2016[/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD="align: right"]24/03/2016[/TD]
[TD="align: right"]24/03/2016[/TD]
[TD="align: right"]5.75[/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD="align: right"]17/08/2016[/TD]
[TD="align: right"]17/08/2016[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD="align: right"]18/08/2016[/TD]
[TD="align: right"]18/08/2016[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD="align: right"]8/04/2016[/TD]
[TD="align: right"]8/04/2016[/TD]
[TD="align: right"]7.6[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]7.6[/TD]
[/TR]
[TR]
[TD]Lee[/TD]
[TD="align: right"]22/01/2016[/TD]
[TD="align: right"]22/01/2016[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD]Lee[/TD]
[TD="align: right"]7/04/2016[/TD]
[TD="align: right"]7/04/2016[/TD]
[TD="align: right"]4.6[/TD]
[/TR]
</tbody>[/TABLE]


At this stage, I need to highlight the consecutive days that an employee has been requesting sick leave. As the aim is to identify who is taking days in a row.

I have been trying to use the conditional formatting à New rule à use a formula, with the following formula:
=(COUNTIF($B$3:$B$500,B3+1)+COUNTIF($B$3:$B$500,B3-1))>0

However, is not working.

Thanks for your help

 

[Joe4]

As the spreadsheet is complex I was going step by step to get the report I needed.

That is a good way of going about it. If you ask too much in one question/thread, you may reduce your chances of getting replies (or at least you may only get partial replies). What we usually recommend though, is to ask you new question in a new thread (even if it is on the same project).

The general rule of thumb is if the question is a directly related follow-up question that is totally dependent on the previous question, then post it in the same thread (and by "totally dependent", I mean the question would not make any sense to someone reading it if they did not see the previous question). Otherwise, post it to a new thread.