Locating first and last non-zero value cells based on value of cell value in COL A

[Darren_workforce]

I have the following formulas that pull the first and last non-zero cells and populate the header (time interval) in the respective column on the MON tab.
=INDEX(MON!$B$5:$AQ$5,MATCH(TRUE,INDEX(MON!B6:AQ6<>0,),0)) first non-zero in the row
=INDEX(MON!$B$5:$AQ$5,MATCH(TRUE,INDEX(MON!B6:AQ6<>0,),1)) last non-zero in the row.

The thing I'd like help with is my AGENT tab. I have a list of employee down column B. If John Smith's name is in B2, then C2 should display 8:00 (first non-zero his row on the MON tab).

Lastly, if the formula is not terribly long, I'd like to try and concatenate C2 so that it would read "first non-zero cell time interval" &" - " &"last non-zero cell time interval"

Any help is greatly appreciated!!

 

[Legacy 143009]

C2:

=LET(m,MATCH($B2,$A$5:$A$8,0),i,INDEX($B$5:$Q$8<>0,m),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))

If it is too long then, you may only use the INDEX parts. Have a nice day

 

[Legacy 143009]

It may be even shorter:

=LET(i,INDEX($B$5:$Q$8<>0,MATCH($B2,$A$5:$A$8,0)),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))

 

[Fluff]

Another option

=LET(f,FILTER(MON!$B$5:$AQ$5,FILTER(MON!B6:AQ100,MON!A6:A100=B2)<>0),"first non-zero cell time interval "&TAKE(f,,1)&" - "&TAKE(f,,-1)&" last non-zero cell time interval")

 

[Darren_workforce]

C2:

=LET(m,MATCH($B2,$A$5:$A$8,0),i,INDEX($B$5:$Q$8<>0,m),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))

If it is too long then, you may only use the INDEX parts. Have a nice day

I used this one and of the 3 provided, this is the closest to doing what I had hoped! One thing I had failed to realize in my explanation is that the employee breaks (b) and lunches (l) are plotted along the same row. Because they break up the 1s, it throws your formula off. Can the formula possibly be adjusted so that it still displays the first non-zero cell along the row and then the hyphen and lastly, the first zero cell after the series of 1s?

 

[Darren_workforce]

It may be even shorter:

=LET(i,INDEX($B$5:$Q$8<>0,MATCH($B2,$A$5:$A$8,0)),TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,i,1)),"hh:mm")))

I made another effort with this one. Apparently I didn't adjust the formula accurately the first time. Seeing this do the trick makes this the better of the 2! My request from before still applies, though: instead of the last non-zero cell being located in the formula, can it be replaced with the first zero value cell after the series of 1s begins?

 

[Darren_workforce]

Another option

=LET(f,FILTER(MON!$B$5:$AQ$5,FILTER(MON!B6:AQ100,MON!A6:A100=B2)<>0),"first non-zero cell time interval "&TAKE(f,,1)&" - "&TAKE(f,,-1)&" last non-zero cell time interval")

I'm getting the #NAME error with this. Apologies I didn't actually need the "first non-zero cell" and "last non-zero cell". Those were just illustrating the values that I wanted to populate within the results cell.

 

[Legacy 143009]

This should work:

=TEXTJOIN(" - ",0,TEXT(INDEX($B$5:$AQ$5,MATCH(TRUE,INDEX($B$6:$AQ$8<>0,MATCH($B2,$A$6:$A$8,0)),0)),"hh:mm"),TEXT(INDEX($B$5:$AQ$5,MAX(INDEX(($B$6:$AQ$8<>0)*COLUMN($B$1:$AQ$1),MATCH($B2,$A$6:$A$8,0)))),"hh:mm"))

 

[Peter_SSs]

See if this one does what you want.

Darren_workforce.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
5		8:00	8:15	8:30	8:45	9:00	9:15	9:30	9:45	10:00	10:15	10:30	10:45
6	John Smith	1	1	1	1	1	1	1	1	1	1	0	0
7	Brad Cooper	0	0	0	0	1	1	1	1	0	0	0	0
8	Mary Moore	0	0	0	0	0	1	0	0	0	0	0	0
MON

Darren_workforce.xlsm
	B	C
1
2	John Smith	8:00 - 10:30
3	Mary Moore	9:15 - 9:30
4	Brad Cooper	9:00 - 10:00
AGENT

Cell Formulas
Range	Formula
C2:C4	C2	=LET(s,TEXTJOIN(REPT(" ",20),1,FILTER(MON!B$5:AQ$5,FILTER(MON!B$6:AQ$20,MON!A$6:A$20=B2)=1)),TRIM(TEXT(LEFT(s,20),"h:mm")&TEXT(RIGHT(s,20)+1/96," - h:mm")))

 

[Darren_workforce]

See if this one does what you want.

Darren_workforce.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
5		8:00	8:15	8:30	8:45	9:00	9:15	9:30	9:45	10:00	10:15	10:30	10:45
6	John Smith	1	1	1	1	1	1	1	1	1	1	0	0
7	Brad Cooper	0	0	0	0	1	1	1	1	0	0	0	0
8	Mary Moore	0	0	0	0	0	1	0	0	0	0	0	0
MON

Darren_workforce.xlsm
	B	C
1
2	John Smith	8:00 - 10:30
3	Mary Moore	9:15 - 9:30
4	Brad Cooper	9:00 - 10:00
AGENT

Cell Formulas
Range	Formula
C2:C4	C2	=LET(s,TEXTJOIN(REPT(" ",20),1,FILTER(MON!B$5:AQ$5,FILTER(MON!B$6:AQ$20,MON!A$6:A$20=B2)=1)),TRIM(TEXT(LEFT(s,20),"h:mm")&TEXT(RIGHT(s,20)+1/96," - h:mm")))

Thank you VERY MUCH!!!! It feels complicated but it did exactly what I was looking for. Supervisors have been wanting a more readable version of my scheduler and this helps make it very easy to transform into just that. Thank you again!!!