Consecutive cell count (with a catch)

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 21, 2014

• #2

What is the result for the sample you posted?

 

Upvote 0

O

oneillc9

New Member

Joined

Oct 21, 2014

Messages

6

• Oct 21, 2014

• #3

The result would be Y given that John has taken 10 consecutive business days off (i.e. 2 weeks at once).

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 21, 2014

• #4

oneillc9 said:

The result would be Y given that John has taken 10 consecutive business days off (i.e. 2 weeks at once).

Click to expand...

But what is the result for the sample you provided?

 

Upvote 0

O

oneillc9

New Member

Joined

Oct 21, 2014

Messages

6

• Oct 21, 2014

• #5

Aladin Akyurek said:

But what is the result for the sample you provided?

Click to expand...

The result for the sample above would be Y. I.e. there are 10 consecutive vacation days marked into the sheet.

Another example below further illustrates this. John will be on vacation for two weeks at one time whereas Mary has taken the same number of days off but will not be on vacation for two weeks at any one time.

	John	Mary
2 weeks consecutive?	Y	N
Thu-01-Jan		v
Fri-02-Jan		v
Sat-03-Jan
Sun-04-Jan
Mon-05-Jan		v
Tue-06-Jan		v
Wed-07-Jan	v
Thu-08-Jan	v
Fri-09-Jan	v	v
Sat-10-Jan
Sun-11-Jan
Mon-12-Jan	v
Tue-13-Jan	v	v
Wed-14-Jan	v	v
Thu-15-Jan	v	v
Fri-16-Jan	v	v
Sat-17-Jan
Sun-18-Jan
Mon-19-Jan	v
Tue-20-Jan	v
Wed-21-Jan
Thu-22-Jan
Fri-23-Jan		v

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

 

Upvote 0

B

blbat

Active Member

Joined

Mar 24, 2010

Messages

338

Office Version

1. 2016
2. 2013

• Oct 21, 2014

• #6

Aladin,

I believe he is after contiguous business days.

I know a simple =COUNTIFS($B$2:$B$16, "V") gives you a Ten count, but I don't know how to check for continuity- maybe some variation of NETWORKDAYS()?

~assuming his "V" for Vacation is begins at B2.

 

Upvote 0

O

oneillc9

New Member

Joined

Oct 21, 2014

Messages

6

• Oct 21, 2014

• #7

If it helps, the largest 'block' of cells that can contain the 10 contiguous business days is 14 (including two weekends). Is there a way to check whether a count of cell values (countif or count) across any 'block' of 14 vertical cells is >=10?

 

Upvote 0

I

iliauk

Board Regular

Joined

Jun 3, 2014

Messages

163

• Oct 21, 2014

• #8

How about this into B2:

Code:

=IF(MAX(FREQUENCY(IF((B$3:B$25="v")*(LEFT($A$3:$A$25,3)<>"Sat")*(LEFT($A$3:$A$25,3)<>"Sun"),ROW(B$3:B$25)),IF((B$3:B$25<>"v")*(LEFT($A$3:$A$25,3)<>"Sat")*(LEFT($A$3:$A$25,3)<>"Sun"),ROW(B$3:B$25))))>=10,"Y","N")

This assumes date goes into column A as a string and that list is from B3 to B25:

John	Mary	Mark
2 weeks consecutive?	Y	N
Thu-01-Jan		v
Fri-02-Jan		v
Sat-03-Jan
Sun-04-Jan
Mon-05-Jan		v
Tue-06-Jan		v
Wed-07-Jan	v
Thu-08-Jan	v
Fri-09-Jan	v	v
Sat-10-Jan
Sun-11-Jan
Mon-12-Jan	v
Tue-13-Jan	v	v
Wed-14-Jan	v	v
Thu-15-Jan	v	v
Fri-16-Jan	v	v
Sat-17-Jan
Sun-18-Jan
Mon-19-Jan	v
Tue-20-Jan	v
Wed-21-Jan
Thu-22-Jan
Fri-23-Jan		v

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

 

Upvote 0

O

oneillc9

New Member

Joined

Oct 21, 2014

Messages

6

• Oct 21, 2014

• #9

Thanks iliauk that works perfectly. i don't really understand how the bins_data section works. If I had a second option 's' that I wanted to also take into account how should I reorganise the formula? For example if it could be 'v' or 's'. Thanks again!

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Oct 21, 2014

• #10

iliauk's solution is based on you having text values in column A (not real dates) - if you have actual dates in A2:A367 then you can use this formula in B1

=IF(MAX(FREQUENCY(IF(WEEKDAY($A2:$A367,2)<6,IF(B2:B367={"v","s"},$A2:$A367)),IF(WEEKDAY($A2:$A367,2)<6,IF((B2:B367="v")+(B2:B367="s")=0,$A2:$A367))))>=10,"Y","N")

confirmed with CTRL+SHIFT+ENTER and copied across

That will take account of both "v" and "s"

 

Upvote 0