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