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:

Full Name	Leave Start Date	Leave End Date	Units Taken
Maria	8/09/2016	8/09/2016	6
Maria	7/12/2016	7/12/2016	7.5
Maria	8/12/2016	8/12/2016	6
Maria	10/12/2016	10/12/2016	6.5
Joe	21/03/2016	21/03/2016	7.5
Joe	22/03/2016	22/03/2016	7.5
Joe	24/03/2016	24/03/2016	5.75
Jenny	17/08/2016	17/08/2016	7.5
Jenny	18/08/2016	18/08/2016	7.5
Anna	8/04/2016	8/04/2016	7.6
Anna	18/04/2016	18/04/2016	7.6
Lee	22/01/2016	22/01/2016	1.5
Lee	7/04/2016	7/04/2016	4.6

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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]

Welcome to the Board!

Let's say that your data is in columns A-D, and starts on row 2.
Then select the range you want to apply this Conditional Formatting to, starting in row 2, and use this CF formula:

=OR(AND($A2=$A3,$C2+1=$B3),AND($A2=$A1,$C1+1=$B2))

 

[Johana H]

Thank you! It works.

I also need to count how many days in a row a person has taken. I've been applying the following formula (using the same example and an additional table with the employee's name), but when I applied for my spreadsheet with nearly 12.000 records is not accurate.

I have applied this formula =SUMPRODUCT(($C$3:$C$15=$K3)*($D$4:$D$16-$D$3:$D$15=1))+(COUNTIF($C$3:$C$15,$K3)>0)

Sheet 1 A B C D Sheet 2 K L Full Name Leave Start Date Leave End Date Units Taken 2 Name No. of days in a row 1 Maria 8/09/2016 8/09/2016 6 3 Maria 2 Maria 7/12/2016 7/12/2016 7.5 4 Joe 2 3 Maria 8/12/2016 8/12/2016 6 5 Jenny 2 4 Maria 10/12/2016 10/12/2016 6.5 6 Anna 5 Joe 21/03/2016 21/03/2016 7.5 7 Lee 6 Joe 22/03/2016 22/03/2016 7.5 7 Joe 24/03/2016 24/03/2016 5.75 8 Jenny 17/08/2016 17/08/2016 7.5 9 Jenny 18/08/2016 18/08/2016 7.5 10 Anna 8/04/2016 8/04/2016 7.6 11 Anna 18/04/2016 18/04/2016 7.6 12 Lee 22/01/2016 22/01/2016 1.5 13 Lee 7/04/2016 7/04/2016 4.6 <colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody> </tbody>

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

Any ideas? or there is other way to count the number of days in a row they have taken.

Thanks

 

[liveinhope]

Hi Johana - not sure if I will be able to help with the actual formulas you want but I have a couple of questions for you - the question may be a bit more complex than as you originally asked

1. You are looking for employees for have "days off in a row" . Are you only concerned if the "days off" are consecutive calendar days ? Your question and Joe4's answer don't pick up if Fred had Friday 3/3/17 off and was also absent on Monday 6/3/17 but if he is a Monday to Friday employee then he did have "days off in a row"

2. you want to know how many total "days off in a row" . in your sample data each person only has one "lot of consecutive days" off. so not too hard. But in my data below Maria has a second lot of "days off in a row" (3 days in a row in March)
I think you might want to know the number of days off for each instance not just total her "days off in a row" as 5

Full Name	Leave Start Date	Leave End Date	Units Taken
Maria	8/09/2016	8/09/2016	6	#VALUE!			start	end	days
Maria	7/12/2016	7/12/2016	7.5	TRUE		Maria	7/12/2016	8/12/2016	2
Maria	8/12/2016	8/12/2016	6	TRUE		Joe	21/03/2016	22/03/2016	2
Maria	10/12/2016	10/12/2016	6.5	FALSE		Jenny	17/08/2016	18/08/2016	2
Joe	21/03/2016	21/03/2016	7.5	TRUE		Maria	1/03/2017	3/03/2017	3
Joe	22/03/2016	22/03/2016	7.5	TRUE		Lee
Joe	24/03/2016	24/03/2016	5.75	FALSE		Anna
Jenny	17/08/2016	17/08/2016	7.5	TRUE
Jenny	18/08/2016	18/08/2016	7.5	TRUE
Anna	8/04/2016	8/04/2016	7.6	FALSE
Anna	18/04/2016	18/04/2016	7.6	FALSE
Lee	22/01/2016	22/01/2016	1.5	FALSE
Lee	7/04/2016	7/04/2016	4.6	FALSE
Maria	1/03/2017	1/03/2017	8	TRUE
Maria	2/03/2017	2/03/2017	8	TRUE
Maria	3/03/2017	3/03/2017	8	TRUE
Joe	3/03/2017	3/03/2017	1	FALSE
Joe	6/03/2017	6/03/2017	8	FALSE

<colgroup><col><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Johana H]

Hi Liveinhope,
Thanks for your quick response.

Your example is just what I need, I was thinking on a way to make it easier, but the way you are presenting the information looks pretty straight forward, as some employees have several lots of consecutive days that I still need to identify.

Regarding to your question the majority of the employees work with roster (I'm able to identify who has roster or not), which would make this harder as they have sometimes 2 separate days off, during the weekdays. If I would apply the example you stated about Fred being absent Friday 3/3/17 and Monday 6/3/17 then he'd have "days off in a row".

I know is a bit complex but I guess including another column with joe's formula =OR(AND($A2=$A3,$C2+2=$B3),AND($A2=$A1,$C1+2=$B2)), would help to identify the employees that may have taken sick days before and after their day off.

Thanks for your help

 

[liveinhope]

unfortunately it's not simple ..

To make it a bit simpler lets pretend that your employees all work Monday to Friday only (so we don't need to look at individuals rosters) and just start off with the "highlight rows where employee has 2 days off I a row" to start

(Come back to the summary part later . that bit's a bit beyond me .)

If you use this formula instead of the one originally from Joe4 =OR(AND($A2=$A3,WORKDAY($C2,1)=$B3),AND($A2=$A1,WORKDAY($C1,1)=$B2)) will highlight where someone had Friday and the following Monday off
But it wont pick up if employee has Friday off, Monday is a public holiday and they also have Tuesday off

To do that you would need a table of public holidays on sheet2 and use this formula =OR(AND($A2=$A3,WORKDAY($C2,1,Sheet2!$A$2:B$20)=$B3),AND($A2=$A1,WORKDAY($C1,1,Sheet2!$A$2:$A$20)=$B2))

(and then you are still limited by my first design rule "Monday to Friday are workdays , Saturday and Sundays are days off".. Someone who has a rostered Saturday off is not identified

 

[Johana H]

Thanks! the formula works perfect for employees that work Mon-Fri.

I would really appreciate if you could help me with the summary.

Thanks again!

 

[liveinhope]

Sorry but the summary bit is beyond me ... I'm pretty sure it will require a VBA solution . Fingers crossed someone who can help there (maybe Joe4 as he's already responded to this thread ) will help out !
I'll be watching because I'd like to know how to do this too

(Ideally you should compare days off against each individuals schedule rather then just treat them all as "wrok Mon-Fri , Sat - Sun off")

 

[Joe4]

I also need to count how many days in a row a person has taken.

That really is a whole another question entirely, and different from your original question.
And as liveinhope mentioned, it is no easy task. It is much more difficult than your original question.
To do it with native Excel functions is beyond my formula capabilities. I would probably try to attack it from a VBA standpoint.

 

[Johana H]

Thanks for your help. As the spreadsheet is complex I was going step by step to get the report I needed. The first part was identifying then create the report.

All the information has been very helpful. thanks.