Count days as a specific status (Red, Yellow, Green - using data validation) until status changes

[daytona12345]

Hard to explain from the title alone. What I would like to be able to do is calculate the number of (days or weeks) a specific project has had a specific health score, before it changes. Assume that the sheet has many many date ranges and there will be a new column added each week with a new status of either Red, Yellow or Green. I could add a column that lists "CURRENT STATUS" as the most recent.

For Example:

PROJECT NAME2 was yellow for 3 months then turned red. Ideally a print out of something like "PROJECT NAME2 was yellow for prior 3 months, current status = red"

 

[JamesCanale]

This is hard to follow.

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	PM Dash	Old Streak	Streak	Old	Streak?	Current Status		3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021
2	Project1	5	1	Green	FALSE	Red		Green	Green	Green	Green	Green	Red
3	Project2	1	2	Green	TRUE	Green		Yellow	Yellow	Red	Red	Green	Green
4	Project3	3	1	Red	FALSE	Yellow		Red	Yellow	Red	Red	Red	Yellow
5
6	Project1 is currently Red but was Green for the prior 5 months.
7	Project2 is currently Green, and has been for 2 months.
8	Project3 is currently Yellow but was Red for the prior 3 months.
Sheet20

Cell Formulas
Range	Formula
I1:M1	I1	=EOMONTH(H1,0)+1
B2:B4	B2	=MATCH(0,(--(INDEX($A2:$AAB2,1,SEQUENCE(MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))-6,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))-1,-1))=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))-1))),0)-1
C2:C4	C2	=MATCH(0,(--(INDEX($A2:$AAB2,1,SEQUENCE(MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))-5,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2))),-1))=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))))),0)-1
D2:D4	D2	=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)))-1)
E2:E4	E2	=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2))))=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2)-1)))
F2:F4	F2	=INDEX($A2:$AAB2,1,MAX(IF(((H2:AAB2))="",0,COLUMN(H2:AAB2))))
A6:A8	A6	=A2&" is currently "&F2& IF(E2,", and has been for "&C2&" months."," but was "&D2&" for the prior "&B2&" months.")

 

[daytona12345]

This looks great, I can certainly work from this. I'm sure this took a fair amount of time to make, it's very appreciated.

 

[JamesCanale]

A good challenge is like gold to me.

 

[daytona12345]

Theoretically this should have never been an issue, but the way we have JIRA setup, I can't do this query in a JIRA dashboard

 

[daytona12345]

having trouble with the eomonth function, just getting errors.

 

[JamesCanale]

eomonth isn't critical to the function of the thing. It's just a solid way of getting the first of the month in a series. The spreadsheet doesn't need it for anything.