Count Instances Between Dates & Other Special Occassion

[MHau5]

In an Excel file I have a list of players and how they performed by each game. The particular information that I'm focused on is the date of the game and whether or not the player scored. What I am trying to do is figure out how much time has lapsed between two dates that a player scored. For example:

Jan-1-2021 (did score)
Jan-5-2021 (didn't score)
Jan-8-2021 (didn't score)
Jan-11-2021 (didn't score)
Jan-13-2021 (did score)

What I'm ultimately looking for is how many games lapsed between when a player scored. In the situation above it would be 3 games between goals (obviously I'm working with a much larger window of time and a larger player list.

NHL Player Game Logs.xlsx
	A	B	C	D
1	gameDate	goals	opponentTeamAbbrev	skaterFullName
2	2019-10-29	2	TOR	Alex Ovechkin
3	2020-03-05	5	WSH	Mika Zibanejad
4	2019-12-23	2	CAR	Mitchell Marner
5	2019-11-04	2	PIT	Brad Marchand
6	2019-10-27	2	DET	Ryan O'Reilly
7	2019-12-28	2	TOR	Ryan Strome
8	2019-12-28	2	NSH	Bryan Rust
9	2019-12-31	3	NYR	James Neal
10	2019-10-08	4	NYI	James Neal
11	2020-02-20	3	OTT	Mark Scheifele
12	2019-11-30	3	DET	Alex Ovechkin
13	2019-10-05	3	OTT	Mika Zibanejad
14	2019-10-10	2	TOR	Nikita Kucherov
15	2019-11-27	2	VAN	Evgeni Malkin
16	2020-01-09	3	NJD	Tony DeAngelo
17	2019-12-29	2	MTL	Jonathan Huberdeau
18	2019-11-16	4	OTT	Jack Eichel
19	2019-12-31	2	CGY	Patrick Kane
20	2019-10-06	4	DAL	Anthony Mantha
21	2020-03-07	3	MIN	Dustin Brown
22	2020-02-01	2	MIN	Torey Krug
23	2020-01-13	2	NYI	Artemi Panarin
24	2020-02-17	3	ANA	Andrew Mangiapane
25	2019-10-29	3	MIN	Alexander Radulov
26	2019-10-16	1	PHI	Connor McDavid
27	2020-02-23	2	VGK	Adam Henrique
28	2019-10-13	2	LAK	Paul Stastny
29	2020-02-18	2	BUF	Jean-Gabriel Pageau
30	2020-03-02	1	NSH	Connor McDavid
31	2020-03-02	4	NSH	Leon Draisaitl
32	2019-11-14	3	COL	Connor McDavid
33	2019-11-21	2	CAR	Claude Giroux
34	2019-10-16	3	CAR	Evander Kane
35	2019-12-12	3	SJS	Artemi Panarin
36	2019-10-30	3	LAK	Brock Boeser
37	2019-12-20	2	NJD	Nicklas Backstrom
38	2020-02-06	2	FLA	Mark Stone
39	2019-10-27	2	NYR	Brad Marchand
40	2019-11-30	2	EDM	Tanner Pearson
41	2019-11-14	0	COL	Leon Draisaitl
42	2020-02-13	4	VGK	Zach Sanford
43	2019-12-15	2	VAN	Max Pacioretty
44	2019-10-14	4	ANA	David Pastrnak
45	2020-01-18	2	TOR	Jonathan Toews
46	2019-10-27	0	NYR	David Pastrnak
47	2019-12-20	3	DAL	Noel Acciari
48	2019-11-30	2	CHI	Joonas Donskoi
49	2019-11-28	2	MTL	Blake Coleman
50	2019-12-07	3	MIN	Sebastian Aho
51	2019-10-31	2	CGY	Austin Watson
Sheet1

 

[Jeffrey Mahoney]

First Try... I added a few lines to test the formula

Book1
	A	B	C	D	E
1	ameDate	goals	opponentTeamAbbrev	skaterFullName	Loss Count
2	2/23/2020	2	VGK	Adam Henrique	NA
3	10/29/2019	2	TOR	Alex Ovechkin	NA
4	11/30/2019	3	DET	Alex Ovechkin	0
5	10/29/2019	3	MIN	Alexander Radulov	NA
6	2/17/2020	3	ANA	Andrew Mangiapane	NA
7	10/6/2019	4	DAL	Anthony Mantha	NA
8	12/12/2019	3	SJS	Artemi Panarin	NA
9	1/13/2020	2	NYI	Artemi Panarin	0
10	10/31/2019	2	CGY	Austin Watson	NA
11	11/28/2019	2	MTL	Blake Coleman	NA
12	10/27/2019	2	NYR	Brad Marchand	NA
13	11/4/2019	2	PIT	Brad Marchand	0
14	10/30/2019	3	LAK	Brock Boeser	NA
15	12/28/2019	2	NSH	Bryan Rust	NA
16	11/21/2019	2	CAR	Claude Giroux	NA
17	10/16/2019	1	PHI	Connor McDavid	NA
18	11/14/2019	0	COL	Connor McDavid	Loss
19	3/2/2020	1	NSH	Connor McDavid	1
20	10/14/2019	4	ANA	David Pastrnak	NA
21	10/27/2019	0	NYR	David Pastrnak	Loss
22	3/7/2020	3	MIN	Dustin Brown	NA
23	10/16/2019	3	CAR	Evander Kane	NA
24	11/27/2019	2	VAN	Evgeni Malkin	NA
25	11/16/2019	4	OTT	Jack Eichel	NA
26	10/8/2019	4	NYI	James Neal	NA
27	12/31/2019	3	NYR	James Neal	0
28	2/18/2020	2	BUF	Jean-Gabriel Pageau	NA
29	12/29/2019	2	MTL	Jonathan Huberdeau	NA
30	1/18/2020	2	TOR	Jonathan Toews	NA
31	11/30/2019	2	CHI	Joonas Donskoi	NA
32	11/14/2019	0	COL	Leon Draisaitl	Loss
33	3/2/2020	4	NSH	Leon Draisaitl	NA
34	2/20/2020	3	OTT	Mark Scheifele	NA
35	2/6/2020	2	FLA	Mark Stone	NA
36	12/15/2019	2	VAN	Max Pacioretty	NA
37	10/5/2019	3	OTT	Mika Zibanejad	NA
38	11/6/2019	0	VAN	Mika Zibanejad	Loss
39	1/31/2020	0	TOR	Mika Zibanejad	Loss
40	3/5/2020	5	WSH	Mika Zibanejad	2
41	12/23/2019	2	CAR	Mitchell Marner	NA
42	12/20/2019	2	NJD	Nicklas Backstrom	NA
43	10/10/2019	2	TOR	Nikita Kucherov	NA
44	12/20/2019	3	DAL	Noel Acciari	NA
45	12/31/2019	2	CGY	Patrick Kane	NA
46	10/13/2019	2	LAK	Paul Stastny	NA
47	10/27/2019	2	DET	Ryan O'Reilly	NA
48	12/28/2019	2	TOR	Ryan Strome	NA
49	12/7/2019	3	MIN	Sebastian Aho	NA
50	11/30/2019	2	EDM	Tanner Pearson	NA
51	1/9/2020	3	NJD	Tony DeAngelo	NA
52	2/1/2020	2	MIN	Torey Krug	NA
53	2/13/2020	4	VGK	Zach Sanford	NA
456 Hank

Cell Formulas
Range	Formula
E2:E53	E2	=IF(B2>0,IF(MAXIFS(A:A,A:A,"<"&A2,D:D,"="&D2,B:B,">"&0)>0,COUNTIFS(A:A,"<"&A2,A:A,">"&MAXIFS(A:A,A:A,"<"&A2,D:D,"="&D2,B:B,">"&0),B:B,"="&0,D:D,"="&D2),"NA"),"Loss")

 

[MHau5]

Thank you! If I wanted it to tell me the number of days between games where a player did score, what would I have to update in the formula to reflect that?

 

[Jeffrey Mahoney]

=IF(B2>0,IF(MAXIFS(A:A,A:A,"<"&A2,D:D,"="&D2,B:B,">"&0)>0,COUNTIFS(A:A,"<"&A2,A:A,">"&MAXIFS(A:A,A:A,"<"&A2,D:D,"="&D2,B:B,">"&0),B:B,"="&0,D:D,"="&D2),"NA"),"Loss")
The red text in the formula finds the previous posted date for that person if the score is not zero. All you have to do is subtract that date from the current date.