Count Consecutive Repeated Values including weekends

[johnbird1988]

Hello,

I would like some help with a Employee absence tracker I have created.

I would like to started recording the number of occurring sick absence in a year. My file looks like below.

The "S"represents a sickness, and for John the number of occurring sick absences are 2 because the 10 and 11 day is a weekend. For Dave the occurring sick absence is 3.

Any help would be amazing.

Thank you,

John

Date

05/01/2015

06/01/20150

07/01/2015

08/01/2015

09/01/2015

10/01/2015

11/01/2015

12/01/2015

13/01/2015

14/01/2015

15/01/2015

16/01/2015

17/01/2015

18/01/2015

Name

John

S

S

S

S

Dave

S

S

S

S

<tbody>
</tbody>

 

[Aladin Akyurek]

Some observations:

1) Your topic title says "including weekends" while your description implies excluding weekends as you word it: "because the 10 and 11 day is a weekend".
2) Why is John 2 and not 3? (John has no S in 10-Jan-2015 and in 11-Jan-2015?

 

[skywriter]

http://www.mrexcel.com/forum/excel-...ances-discounting-weekends-bank-holidays.html

 

[johnbird1988]

Hello Aladin,

I have this formula to work out the number of instances of "S" appearing within a range. The below formula work brilliantly, however, I cannot get it to incorporate Bank Holidays. I have all the bank holidays listed in a named range called "Holidays".

John is 2 because the 10 and 11 is a weekend and he is still sick on the Monday. I would like to count this as a continues sickness. The formula above does this however I need it to include bank holidays from a named range called holidays. Do you think you could help, I have been trying and trying all day.

{=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0))+ISNUMBER(MATCH(IF(ISNUMBER(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),$J$6:$NK$6,"#"),IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0)),IF($J$9:$NK$9="Fri",$J$6:$NK$6+1,IF($J$9:$NK$9="Mon",$J$6:$NK$6-1))),0)),COLUMN($J10:$NK10)),IF(ISNA(MATCH($J10:$NK10,{"S"},0))*ISNA(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),COLUMN($J10:$NK10))),1))}

Thank you for your help

John

 

[Aladin Akyurek]

Hello Aladin,

I have this formula to work out the number of instances of "S" appearing within a range. The below formula work brilliantly, however, I cannot get it to incorporate Bank Holidays. I have all the bank holidays listed in a named range called "Holidays".

John is 2 because the 10 and 11 is a weekend and he is still sick on the Monday. I would like to count this as a continues sickness. The formula above does this however I need it to include bank holidays from a named range called holidays. Do you think you could help, I have been trying and trying all day.

{=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0))+ISNUMBER(MATCH(IF(ISNUMBER(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),$J$6:$NK$6,"#"),IF(ISNUMBER(MATCH($J10:$NK10,{"S"},0)),IF($J$9:$NK$9="Fri",$J$6:$NK$6+1,IF($J$9:$NK$9="Mon",$J$6:$NK$6-1))),0)),COLUMN($J10:$NK10)),IF(ISNA(MATCH($J10:$NK10,{"S"},0))*ISNA(MATCH($J$9:$NK$9,{"Sat","Sun"},0)),COLUMN($J10:$NK10))),1))}

Thank you for your help

John

In what follows, I'm going disregard the formula you posted although I recognize much of it.

I'll assume the sample you posted with which I worked. I consider the sample to be in A1:O4 of Sheet1, where A3:A4 houses names, B1:O1 dates, and B3:O4 "S" markings in indication of sick absences.

1) including weekends when enclosed between S markings...

Define first wdates using Formulas | Name Manager as referring to:

=SMALL(IF(WEEKDAY(Sheet1!$B$1:$O$1,2) < 6,Sheet1!$B$1:$O$1),
  ROW(INDIRECT("1:"&SUM(IF(WEEKDAY(Sheet1!$B$1:$O$1) < 6,1)))))

P3 (or somewhere else more convenient), control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(
   IF(ISNUMBER(MATCH(wdates,IF(B3:O3="S",$B$1:$O$1),0)),wdates),
   IF(1-ISNUMBER(MATCH(wdates,IF(B3:O3="S",$B$1:$O$1),0)),
    wdates)),1))

2) including weekends and bank holidays when enclosed between S markings, we just need to extend the definition of wdates...

=SMALL(IF(WEEKDAY(Sheet1!$B$1:$O$1,2) < 6,
  IF(ISNA(MATCH(Sheet1!$B$1:$O$1,Holidays,0)),
   Sheet1!$B$1:$O$1)),
  ROW(INDIRECT("1:"&SUM(IF(WEEKDAY(Sheet1!$B$1:$O$1) < 6,
  IF(ISNA(MATCH(Sheet1!$B$1:$O$1,Holidays,0)),1))))))

 

[johnbird1988]

Hi Aladin,

Thank you for the above. I have just tested it in my actual file and it is only partly working. I am using the named range and formula below:

wdates

=SMALL(IF(WEEKDAY('Input Sheet'!$J$6:$NK$6,2)<6,IF(ISNA(MATCH('Input Sheet'!$J$6:$NK$6,Holiday,0)),'Input Sheet'!$J$6:$NK$6)),ROW(INDIRECT("6:"&SUM(IF(WEEKDAY('Input Sheet'!$J$6:$NK$6)<6,IF(ISNA(MATCH('Input Sheet'!$J$6:$NK$6,Holiday,0)),1))))))

#

Cell Formula

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(wdates,IF($J10:$NK10="S",$J$6:$NK$6),0)),wdates),IF(1-ISNUMBER(MATCH(wdates,IF($J10:$NK10="S",$J$6:$NK$6),0)),wdates)),1))

As you can see I have extended the range and also the dates are in row 6 and row 10 is the first employee.

For some reason this only starts to calculate from column R and not J and I don't know why. If I enter 'S' from column R onward it records as it should be but noting from columns J to Q.

Thank you for all your help so far.

John

 

[Aladin Akyurek]

I did not use something like "6:"&... See the workbook that implements the suggested set up: https://dl.dropboxusercontent.com/u...utive Repeated Values including weekends.xlsx

 

[johnbird1988]

I did not use something like "6:"&... See the workbook that implements the suggested set up: https://dl.dropboxusercontent.com/u...utive Repeated Values including weekends.xlsx

That is great thank you. I though as the dates are in row 6 I had to change the row from 1 to 6. Just changed it back and it is all working now. Thank you very much for your help.

John

 

[Aladin Akyurek]

That is great thank you. I though as the dates are in row 6 I had to change the row from 1 to 6. Just changed it back and it is all working now. Thank you very much for your help.

John

You are welcome.

 

[johnbird1988]

Hello Aladin,

Just one more thing related to this post.

If I now only want to just count the number of times "S" appeared in a weekday (again excluding weekends and holidays), how can I achieve this, unlike the above where I want to count occurring "S" now I just want to count the number of "S".

I have tried this formula but it not work correctly.

[COLOR=#333333]=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*($J6:$NK6<>Holiday))[/COLOR]

Any help would be great.

Thank you,

John