Count of consecutive 02 days OFF and 04 days OFF

[Bahadur22]

Dear Excel Guru's Kindly need your help to get the consecutive 02 days OFF and 04 days OFF count for my staff roster separately.
I want to get the values as shown in green cell. Attached excel file is for your reference and above image as well.

Days_OFF_Formula_Help.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2																Result Needed
3	Name	01-Nov	02-Nov	03-Nov	04-Nov	05-Nov	06-Nov	07-Nov	08-Nov	09-Nov	10-Nov	11-Nov	12-Nov	13-Nov		2 Consective OFF	4 Consective OFF
4	Jack	M	M	M	M	M	OFF	OFF	M	M	M	OFF	M	M		1	0
5	Mike	M	M	M	M	M	OFF	OFF	OFF	OFF	M	M	M	M		0	1
6	Tim	M	M	M	M	OFF	OFF	M	M	M	M	M	OFF	OFF		2	0
7
8				M	09:00 - 17:00
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F8	Cell Value	contains "M"	text	NO
F8	Cell Value	="OFF"	text	NO
D4:P6,G8	Cell Value	contains "M"	text	NO
D4:P6,G8	Cell Value	="OFF"	text	NO

 

[etaf]

answered on other forum

Count of Consecutive Days OFF Formula

Dear Excel Guru's Kindly need your help to get the consecutive 02 days OFF and 04 days OFF count for my staff roster separately. I want to get the values as shown in green cell. Attached excel file is for your reference and above image as well.

chandoo.org

also here with a search

Counting consecutive text values in a single row

If I have a row and in each column of that row there is a letter value. If there is let's say an "X" in 5 consecutive columns of that row and I only want to count 3 of them, then 10 columns of "W's" then another 5 columns of "X's" and once again only want to count 3 of them as well, what is the...

www.mrexcel.com

 

[lrobbo314]

One way using Power Query.

Book1
	S	T
3	2 Days	4 Days
4	1	0
5	0	1
6	2	0
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOP = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    GroupOuter = Table.Group(UOP, {"Name"}, {{"Count", each _, type table [Name=text, Attribute=text, Value=text]}}),
    GroupInner = Table.TransformColumns(GroupOuter,{{"Count", each Table.Group(_,{"Value"}, {{"Group", each Table.RowCount(_)}},GroupKind.Local)}}),
    SelectOff = Table.TransformColumns(GroupInner,{{"Count", each Table.SelectRows(_, each _[Value]="OFF")}}),
    Twos = Table.AddColumn(SelectOff, "2 Days", each List.Count(List.Select([Count][Group], each _ =2))),
    Fours = Table.AddColumn(Twos, "4 Days", each List.Count(List.Select([Count][Group], each _ =4))),
    ROC = Table.SelectColumns(Fours,{"2 Days", "4 Days"})
in
    ROC

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count of Consecutive Days OFF Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.