NHL Hockey Days between games counter

AYouQueTai

Board Regular
Joined
Sep 14, 2019
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hello,

Is there a formula that would count the days between a teams last game? The answer can be found in multiple ranges because they could have hosted their last game or played on the road.

For example, Nashville played at home on October 12th and their last game prior to oct 12 was away on October 10th.
So the answer would be 2 in cell H16.
Tampa Bay played as visitor on October 14 and their last game was when they were at home on October 10th.
So the answer would be 4 in Cell F24 etc...
A zero could be displayed for their first games of the season

Thanks,

NHL 2023-2024.xlsx
ABCDEFGHI
1GameIDStatusDayDateTime (AST)VDBGVisitorHDBGHome
2020001CompletedTuesdayOct-1018:30NashvilleTampa Bay
3020002CompletedTuesdayOct-1021:00ChicagoPittsburgh
4020003CompletedTuesdayOct-1023:45SeattleVegas
5020004CompletedWednesdayOct-1120:00OttawaCarolina
6020005CompletedWednesdayOct-1120:00MontrealToronto
7020006CompletedWednesdayOct-1120:30ChicagoBoston
8020007CompletedWednesdayOct-1123:00WinnipegCalgary
9020008CompletedWednesdayOct-1123:15ColoradoLos Angeles
10020009CompletedWednesdayOct-1123:15EdmontonVancouver
11020010CompletedThursdayOct-1220:00NY RangersBuffalo
12020011CompletedThursdayOct-1220:00PhiladelphiaColumbus
13020012CompletedThursdayOct-1220:30DetroitNew Jersey
14020013CompletedThursdayOct-1221:00St-LouisDallas
15020014CompletedThursdayOct-1221:00FloridaMinnesota
16020015CompletedThursdayOct-1221:00SeattleNashville
17020016CompletedThursdayOct-1223:30VegasSan Jose
18020017CompletedFridayOct-1320:00ArizonaNew Jersey
19020018CompletedFridayOct-1320:30PittsburghWashington
20020019CompletedSaturdayOct-1414:15PhiladelphiaOttawa
21020020CompletedSaturdayOct-1417:15FloridaWinnipeg
22020021CompletedSaturdayOct-1420:00NashvilleBoston
23020022CompletedSaturdayOct-1420:00NY RangersColumbus
24020023CompletedSaturdayOct-1420:00Tampa BayDetroit
25020024CompletedSaturdayOct-1420:00ChicagoMontreal
26020025CompletedSaturdayOct-1420:00CalgaryPittsburgh
27020026CompletedSaturdayOct-1420:00MinnesotaToronto
28020027CompletedSaturdayOct-1420:30BuffaloNY Islanders
29020028CompletedSaturdayOct-1421:00SeattleSt-Louis
30020029CompletedSaturdayOct-1423:00VancouverEdmonton
31020030CompletedSaturdayOct-1423:00ColoradoSan Jose
32020031CompletedSaturdayOct-1423:00AnaheimVegas
33020032CompletedSaturdayOct-1423:30CarolinaLos Angeles
34020033CompletedSundayOct-1520:00Tampa BayOttawa
35020034CompletedSundayOct-1521:30CarolinaAnaheim
36020035CompletedMondayOct-1620:00DetroitColumbus
37020036CompletedMondayOct-1620:00FloridaNew Jersey
38020037CompletedMondayOct-1620:00ArizonaNY Rangers
39020038CompletedMondayOct-1620:00ChicagoToronto
40020039CompletedMondayOct-1620:00CalgaryWashington
41020042CompletedTuesdayOct-1719:00VancouverPhiladelphia
42020041CompletedTuesdayOct-1720:00MinnesotaMontreal
43020040CompletedTuesdayOct-1720:30Tampa BayBuffalo
44020043CompletedTuesdayOct-1720:30ArizonaNY Islanders
45020044CompletedTuesdayOct-1721:00EdmontonNashville
46020045CompletedTuesdayOct-1721:00Los AngelesWinnipeg
47020046CompletedTuesdayOct-1723:00ColoradoSeattle
48020047CompletedTuesdayOct-1723:30CarolinaSan Jose
49020048CompletedTuesdayOct-1723:30DallasVegas
50020049CompletedWednesdayOct-1820:00WashingtonOttawa
51020050CompletedWednesdayOct-1820:30PittsburghDetroit
52020051CompletedThursdayOct-1920:00CalgaryBuffalo
53020052CompletedThursdayOct-1920:00TorontoFlorida
54020053CompletedThursdayOct-1920:00NashvilleNY Rangers
55020055CompletedThursdayOct-1920:00VancouverTampa Bay
56020054CompletedThursdayOct-1920:30EdmontonPhiladelphia
57020056CompletedThursdayOct-1921:00Los AngelesMinnesota
58020057CompletedThursdayOct-1921:00ArizonaSt-Louis
59020058CompletedThursdayOct-1921:00VegasWinnipeg
60020059CompletedThursdayOct-1923:00DallasAnaheim
61020060CompletedThursdayOct-1923:00CarolinaSeattle
62020061CompletedThursdayOct-1923:30ChicagoColorado
63020062CompletedThursdayOct-1923:30BostonSan Jose
64020063CompletedFridayOct-2020:00CalgaryColumbus
65020064CompletedFridayOct-2020:30New JerseyNY Islanders
66020065CompletedSaturdayOct-2114:00DetroitOttawa
67020066CompletedSaturdayOct-2116:00AnaheimArizona
68020067CompletedSaturdayOct-2120:00NY IslandersBuffalo
69020068CompletedSaturdayOct-2120:00VancouverFlorida
70020069CompletedSaturdayOct-2120:00WashingtonMontreal
71020070CompletedSaturdayOct-2120:00TorontoTampa Bay
72020071CompletedSaturdayOct-2121:00VegasChicago
73020072CompletedSaturdayOct-2121:00PhiladelphiaDallas
74020073CompletedSaturdayOct-2121:00ColumbusMinnesota
75020074CompletedSaturdayOct-2121:00San JoseNashville
76020075CompletedSaturdayOct-2121:00PittsburghSt-Louis
77020076CompletedSaturdayOct-2122:00CarolinaColorado
78020077CompletedSaturdayOct-2123:00WinnipegEdmonton
79020078CompletedSaturdayOct-2123:00NY RangersSeattle
80020079CompletedSaturdayOct-2123:30BostonLos Angeles
81020080CompletedSundayOct-2218:00CalgaryDetroit
82020081CompletedSundayOct-2221:30BostonAnaheim
83020082CompletedMondayOct-2320:00MontrealBuffalo
84020083CompletedTuesdayOct-2419:00TorontoWashington
85020085CompletedTuesdayOct-2419:45BuffaloOttawa
86020086CompletedTuesdayOct-2420:00CarolinaTampa Bay
87020087CompletedTuesdayOct-2420:00New JerseyMontreal
88020088CompletedTuesdayOct-2420:30DallasPittsburgh
89020089CompletedTuesdayOct-2420:45San JoseFlorida
90020090CompletedTuesdayOct-2421:00ColoradoNY Islanders
91020091CompletedTuesdayOct-2421:15SeattleDetroit
92020084CompletedTuesdayOct-2421:30AnaheimColumbus
93020092CompletedTuesdayOct-2421:45BostonChicago
94020093CompletedTuesdayOct-2421:45St-LouisWinnipeg
95020094CompletedTuesdayOct-2422:00EdmontonMinnesota
96020095CompletedTuesdayOct-2422:15VancouverNashville
97020096CompletedTuesdayOct-2422:45NY RangersCalgary
98020097CompletedTuesdayOct-2423:30ArizonaLos Angeles
99020098CompletedTuesdayOct-2423:45PhiladelphiaVegas
100020099CompletedWednesdayOct-2520:30WashingtonNew Jersey
Schedule
Cell Formulas
RangeFormula
C2:C100C2=TEXT([@Date],"dddd")
Cells with Data Validation
CellAllowCriteria
B2:B100List=INDIRECT("StatusDrop[Status]")
G2:I100List=INDIRECT("Teams[Teams]")
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,224,550
Messages
6,179,455
Members
452,915
Latest member
hannnahheileen

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
Back
Top