Formulas for pulling data from volunteer shift log

[salange]

I am tracking volunteer shifts in a spreadsheet that looks like this:

https://photos.google.com/share/AF1...?key=cXhJblNNd2dyN0g3OVZ3ZFBVeVpzbGdJdWxuUjN3

I want two forumulas:

1- A formula that identifies the most recent date on which each vol worked. It should return 1/13 for Olivera in the first row, 1/15 for McDaniel in the second row, etc. Seems like it should be easy but I'm struggling to do it neatly.

2- A formula that identifies the longest "streak" of days without volunteering for each person. In other words, most days between shifts. Not sure how to get started with this one.

Thanks in advance for any help you can provide!

 

[VBA Geek]

Excel 2013/2016
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2		M	Tu	W	Th	F	Sa	Su	M	Tu	W	Th	F	Sa	Su	M	Tu	W	Th	F	Sa	Su
3		1/1	2/1	3/1	4/1	5/1	6/1	7/1	8/1	9/1	10/1	11/1	12/1	13/1	14/1	15/1	16/1	17/1	18/1	19/1	20/1	21/1	Most Rec Dt	Most Days in between
4	Olivera												1										12/1	11
5	Daniel															1							15/1	14
6	Donald													0.5									13/1	12
7	Book											2											11/1	10
8	Paugett																					1	21/1	20
9	Wall					1																	5/1	16
10	Gilmore																2						16/1	15
11	Ward									1													9/1	12
12	Uro												2										12/1	11
13
Sheet1

Excel 2013/2016
	Y
4	12/1
Sheet1

Cell Formulas
Range	Formula
Y4	=LOOKUP(10^100,$D4:$X4,$D$3:$X$3)

Excel 2013/2016
	Z
4	11
Sheet1

Cell Formulas
Range	Formula
Z4	{=MAX(FREQUENCY(IF(C4:X4="",COLUMN(C4:X4)),IF(C4:X4<>"",COLUMN(C4:X4))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[salange]

The first answer is perfect - thank you!\

The second answer appears to be counting "the future" (and presumably, "the past",) as a gap. I need it to count only the days that are actually between two shifts, not days that are "before the first" and "after the last" shift. Any tweak that will get the job done?

Thanks in advance!

 

[VBA Geek]

you need to replace the reference that is inside the formula (C4:X4) with:

INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0))

so it becomes (always with Control Shift Enter):

=MAX(FREQUENCY(IF(INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0))="",COLUMN(INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0)))),IF(INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0))<>"",COLUMN(INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0))))))

If you dont want the formula to be this long, I would select any cell in row 4 in my example, and add a Named Range called, for instance, FirstLastShift and add this a formula:

INDEX($D4:$X4,MATCH(10^100,$D4:$X4)):INDEX($D4:$X4,MATCH(TRUE,ISNUMBER($D4:$X4),0))

then you could have:

=IFERROR(MAX(FREQUENCY(IF(FirstLastShift="",COLUMN(FirstLastShift)),IF(FirstLastShift<>"",COLUMN(FirstLastShift)))),"Only One or No Shifts!")

 

[salange]

That does it. I added in a little IF() to turn the "one shift only" people into blanks instead of errors, but otherwise, perfect. Thank you so much!!