Count Instances Between Dates & Other Special Occassion

MHau5

New Member
Joined
Oct 23, 2021
Messages
21
Office Version
  1. 2019
Platform
  1. Windows
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
ABCD
1gameDategoalsopponentTeamAbbrevskaterFullName
22019-10-292TORAlex Ovechkin
32020-03-055WSHMika Zibanejad
42019-12-232CARMitchell Marner
52019-11-042PITBrad Marchand
62019-10-272DETRyan O'Reilly
72019-12-282TORRyan Strome
82019-12-282NSHBryan Rust
92019-12-313NYRJames Neal
102019-10-084NYIJames Neal
112020-02-203OTTMark Scheifele
122019-11-303DETAlex Ovechkin
132019-10-053OTTMika Zibanejad
142019-10-102TORNikita Kucherov
152019-11-272VANEvgeni Malkin
162020-01-093NJDTony DeAngelo
172019-12-292MTLJonathan Huberdeau
182019-11-164OTTJack Eichel
192019-12-312CGYPatrick Kane
202019-10-064DALAnthony Mantha
212020-03-073MINDustin Brown
222020-02-012MINTorey Krug
232020-01-132NYIArtemi Panarin
242020-02-173ANAAndrew Mangiapane
252019-10-293MINAlexander Radulov
262019-10-161PHIConnor McDavid
272020-02-232VGKAdam Henrique
282019-10-132LAKPaul Stastny
292020-02-182BUFJean-Gabriel Pageau
302020-03-021NSHConnor McDavid
312020-03-024NSHLeon Draisaitl
322019-11-143COLConnor McDavid
332019-11-212CARClaude Giroux
342019-10-163CAREvander Kane
352019-12-123SJSArtemi Panarin
362019-10-303LAKBrock Boeser
372019-12-202NJDNicklas Backstrom
382020-02-062FLAMark Stone
392019-10-272NYRBrad Marchand
402019-11-302EDMTanner Pearson
412019-11-140COLLeon Draisaitl
422020-02-134VGKZach Sanford
432019-12-152VANMax Pacioretty
442019-10-144ANADavid Pastrnak
452020-01-182TORJonathan Toews
462019-10-270NYRDavid Pastrnak
472019-12-203DALNoel Acciari
482019-11-302CHIJoonas Donskoi
492019-11-282MTLBlake Coleman
502019-12-073MINSebastian Aho
512019-10-312CGYAustin Watson
Sheet1
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
First Try... I added a few lines to test the formula

Book1
ABCDE
1ameDategoalsopponentTeamAbbrevskaterFullNameLoss Count
22/23/20202VGKAdam HenriqueNA
310/29/20192TORAlex OvechkinNA
411/30/20193DETAlex Ovechkin0
510/29/20193MINAlexander RadulovNA
62/17/20203ANAAndrew MangiapaneNA
710/6/20194DALAnthony ManthaNA
812/12/20193SJSArtemi PanarinNA
91/13/20202NYIArtemi Panarin0
1010/31/20192CGYAustin WatsonNA
1111/28/20192MTLBlake ColemanNA
1210/27/20192NYRBrad MarchandNA
1311/4/20192PITBrad Marchand0
1410/30/20193LAKBrock BoeserNA
1512/28/20192NSHBryan RustNA
1611/21/20192CARClaude GirouxNA
1710/16/20191PHIConnor McDavidNA
1811/14/20190COLConnor McDavidLoss
193/2/20201NSHConnor McDavid1
2010/14/20194ANADavid PastrnakNA
2110/27/20190NYRDavid PastrnakLoss
223/7/20203MINDustin BrownNA
2310/16/20193CAREvander KaneNA
2411/27/20192VANEvgeni MalkinNA
2511/16/20194OTTJack EichelNA
2610/8/20194NYIJames NealNA
2712/31/20193NYRJames Neal0
282/18/20202BUFJean-Gabriel PageauNA
2912/29/20192MTLJonathan HuberdeauNA
301/18/20202TORJonathan ToewsNA
3111/30/20192CHIJoonas DonskoiNA
3211/14/20190COLLeon DraisaitlLoss
333/2/20204NSHLeon DraisaitlNA
342/20/20203OTTMark ScheifeleNA
352/6/20202FLAMark StoneNA
3612/15/20192VANMax PaciorettyNA
3710/5/20193OTTMika ZibanejadNA
3811/6/20190VANMika ZibanejadLoss
391/31/20200TORMika ZibanejadLoss
403/5/20205WSHMika Zibanejad2
4112/23/20192CARMitchell MarnerNA
4212/20/20192NJDNicklas BackstromNA
4310/10/20192TORNikita KucherovNA
4412/20/20193DALNoel AcciariNA
4512/31/20192CGYPatrick KaneNA
4610/13/20192LAKPaul StastnyNA
4710/27/20192DETRyan O'ReillyNA
4812/28/20192TORRyan StromeNA
4912/7/20193MINSebastian AhoNA
5011/30/20192EDMTanner PearsonNA
511/9/20203NJDTony DeAngeloNA
522/1/20202MINTorey KrugNA
532/13/20204VGKZach SanfordNA
456 Hank
Cell Formulas
RangeFormula
E2:E53E2=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

New Member
Joined
Oct 23, 2021
Messages
21
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
=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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,845
Messages
5,833,936
Members
430,247
Latest member
w9u5280o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top