Count Instances Between Dates & Other Special Occassion


New Member
Oct 23, 2021
Office Version
  1. 2019
  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
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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
First Try... I added a few lines to test the formula

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
Upvote 0
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?
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

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
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 "".
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