AYouQueTai
Board Regular
- Joined
- Sep 14, 2019
- Messages
- 50
- Office Version
- 365
- Platform
- 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,
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 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | GameID | Status | Day | Date | Time (AST) | VDBG | Visitor | HDBG | Home | ||
| 2 | 020001 | Completed | Tuesday | Oct-10 | 18:30 | Nashville | Tampa Bay | ||||
| 3 | 020002 | Completed | Tuesday | Oct-10 | 21:00 | Chicago | Pittsburgh | ||||
| 4 | 020003 | Completed | Tuesday | Oct-10 | 23:45 | Seattle | Vegas | ||||
| 5 | 020004 | Completed | Wednesday | Oct-11 | 20:00 | Ottawa | Carolina | ||||
| 6 | 020005 | Completed | Wednesday | Oct-11 | 20:00 | Montreal | Toronto | ||||
| 7 | 020006 | Completed | Wednesday | Oct-11 | 20:30 | Chicago | Boston | ||||
| 8 | 020007 | Completed | Wednesday | Oct-11 | 23:00 | Winnipeg | Calgary | ||||
| 9 | 020008 | Completed | Wednesday | Oct-11 | 23:15 | Colorado | Los Angeles | ||||
| 10 | 020009 | Completed | Wednesday | Oct-11 | 23:15 | Edmonton | Vancouver | ||||
| 11 | 020010 | Completed | Thursday | Oct-12 | 20:00 | NY Rangers | Buffalo | ||||
| 12 | 020011 | Completed | Thursday | Oct-12 | 20:00 | Philadelphia | Columbus | ||||
| 13 | 020012 | Completed | Thursday | Oct-12 | 20:30 | Detroit | New Jersey | ||||
| 14 | 020013 | Completed | Thursday | Oct-12 | 21:00 | St-Louis | Dallas | ||||
| 15 | 020014 | Completed | Thursday | Oct-12 | 21:00 | Florida | Minnesota | ||||
| 16 | 020015 | Completed | Thursday | Oct-12 | 21:00 | Seattle | Nashville | ||||
| 17 | 020016 | Completed | Thursday | Oct-12 | 23:30 | Vegas | San Jose | ||||
| 18 | 020017 | Completed | Friday | Oct-13 | 20:00 | Arizona | New Jersey | ||||
| 19 | 020018 | Completed | Friday | Oct-13 | 20:30 | Pittsburgh | Washington | ||||
| 20 | 020019 | Completed | Saturday | Oct-14 | 14:15 | Philadelphia | Ottawa | ||||
| 21 | 020020 | Completed | Saturday | Oct-14 | 17:15 | Florida | Winnipeg | ||||
| 22 | 020021 | Completed | Saturday | Oct-14 | 20:00 | Nashville | Boston | ||||
| 23 | 020022 | Completed | Saturday | Oct-14 | 20:00 | NY Rangers | Columbus | ||||
| 24 | 020023 | Completed | Saturday | Oct-14 | 20:00 | Tampa Bay | Detroit | ||||
| 25 | 020024 | Completed | Saturday | Oct-14 | 20:00 | Chicago | Montreal | ||||
| 26 | 020025 | Completed | Saturday | Oct-14 | 20:00 | Calgary | Pittsburgh | ||||
| 27 | 020026 | Completed | Saturday | Oct-14 | 20:00 | Minnesota | Toronto | ||||
| 28 | 020027 | Completed | Saturday | Oct-14 | 20:30 | Buffalo | NY Islanders | ||||
| 29 | 020028 | Completed | Saturday | Oct-14 | 21:00 | Seattle | St-Louis | ||||
| 30 | 020029 | Completed | Saturday | Oct-14 | 23:00 | Vancouver | Edmonton | ||||
| 31 | 020030 | Completed | Saturday | Oct-14 | 23:00 | Colorado | San Jose | ||||
| 32 | 020031 | Completed | Saturday | Oct-14 | 23:00 | Anaheim | Vegas | ||||
| 33 | 020032 | Completed | Saturday | Oct-14 | 23:30 | Carolina | Los Angeles | ||||
| 34 | 020033 | Completed | Sunday | Oct-15 | 20:00 | Tampa Bay | Ottawa | ||||
| 35 | 020034 | Completed | Sunday | Oct-15 | 21:30 | Carolina | Anaheim | ||||
| 36 | 020035 | Completed | Monday | Oct-16 | 20:00 | Detroit | Columbus | ||||
| 37 | 020036 | Completed | Monday | Oct-16 | 20:00 | Florida | New Jersey | ||||
| 38 | 020037 | Completed | Monday | Oct-16 | 20:00 | Arizona | NY Rangers | ||||
| 39 | 020038 | Completed | Monday | Oct-16 | 20:00 | Chicago | Toronto | ||||
| 40 | 020039 | Completed | Monday | Oct-16 | 20:00 | Calgary | Washington | ||||
| 41 | 020042 | Completed | Tuesday | Oct-17 | 19:00 | Vancouver | Philadelphia | ||||
| 42 | 020041 | Completed | Tuesday | Oct-17 | 20:00 | Minnesota | Montreal | ||||
| 43 | 020040 | Completed | Tuesday | Oct-17 | 20:30 | Tampa Bay | Buffalo | ||||
| 44 | 020043 | Completed | Tuesday | Oct-17 | 20:30 | Arizona | NY Islanders | ||||
| 45 | 020044 | Completed | Tuesday | Oct-17 | 21:00 | Edmonton | Nashville | ||||
| 46 | 020045 | Completed | Tuesday | Oct-17 | 21:00 | Los Angeles | Winnipeg | ||||
| 47 | 020046 | Completed | Tuesday | Oct-17 | 23:00 | Colorado | Seattle | ||||
| 48 | 020047 | Completed | Tuesday | Oct-17 | 23:30 | Carolina | San Jose | ||||
| 49 | 020048 | Completed | Tuesday | Oct-17 | 23:30 | Dallas | Vegas | ||||
| 50 | 020049 | Completed | Wednesday | Oct-18 | 20:00 | Washington | Ottawa | ||||
| 51 | 020050 | Completed | Wednesday | Oct-18 | 20:30 | Pittsburgh | Detroit | ||||
| 52 | 020051 | Completed | Thursday | Oct-19 | 20:00 | Calgary | Buffalo | ||||
| 53 | 020052 | Completed | Thursday | Oct-19 | 20:00 | Toronto | Florida | ||||
| 54 | 020053 | Completed | Thursday | Oct-19 | 20:00 | Nashville | NY Rangers | ||||
| 55 | 020055 | Completed | Thursday | Oct-19 | 20:00 | Vancouver | Tampa Bay | ||||
| 56 | 020054 | Completed | Thursday | Oct-19 | 20:30 | Edmonton | Philadelphia | ||||
| 57 | 020056 | Completed | Thursday | Oct-19 | 21:00 | Los Angeles | Minnesota | ||||
| 58 | 020057 | Completed | Thursday | Oct-19 | 21:00 | Arizona | St-Louis | ||||
| 59 | 020058 | Completed | Thursday | Oct-19 | 21:00 | Vegas | Winnipeg | ||||
| 60 | 020059 | Completed | Thursday | Oct-19 | 23:00 | Dallas | Anaheim | ||||
| 61 | 020060 | Completed | Thursday | Oct-19 | 23:00 | Carolina | Seattle | ||||
| 62 | 020061 | Completed | Thursday | Oct-19 | 23:30 | Chicago | Colorado | ||||
| 63 | 020062 | Completed | Thursday | Oct-19 | 23:30 | Boston | San Jose | ||||
| 64 | 020063 | Completed | Friday | Oct-20 | 20:00 | Calgary | Columbus | ||||
| 65 | 020064 | Completed | Friday | Oct-20 | 20:30 | New Jersey | NY Islanders | ||||
| 66 | 020065 | Completed | Saturday | Oct-21 | 14:00 | Detroit | Ottawa | ||||
| 67 | 020066 | Completed | Saturday | Oct-21 | 16:00 | Anaheim | Arizona | ||||
| 68 | 020067 | Completed | Saturday | Oct-21 | 20:00 | NY Islanders | Buffalo | ||||
| 69 | 020068 | Completed | Saturday | Oct-21 | 20:00 | Vancouver | Florida | ||||
| 70 | 020069 | Completed | Saturday | Oct-21 | 20:00 | Washington | Montreal | ||||
| 71 | 020070 | Completed | Saturday | Oct-21 | 20:00 | Toronto | Tampa Bay | ||||
| 72 | 020071 | Completed | Saturday | Oct-21 | 21:00 | Vegas | Chicago | ||||
| 73 | 020072 | Completed | Saturday | Oct-21 | 21:00 | Philadelphia | Dallas | ||||
| 74 | 020073 | Completed | Saturday | Oct-21 | 21:00 | Columbus | Minnesota | ||||
| 75 | 020074 | Completed | Saturday | Oct-21 | 21:00 | San Jose | Nashville | ||||
| 76 | 020075 | Completed | Saturday | Oct-21 | 21:00 | Pittsburgh | St-Louis | ||||
| 77 | 020076 | Completed | Saturday | Oct-21 | 22:00 | Carolina | Colorado | ||||
| 78 | 020077 | Completed | Saturday | Oct-21 | 23:00 | Winnipeg | Edmonton | ||||
| 79 | 020078 | Completed | Saturday | Oct-21 | 23:00 | NY Rangers | Seattle | ||||
| 80 | 020079 | Completed | Saturday | Oct-21 | 23:30 | Boston | Los Angeles | ||||
| 81 | 020080 | Completed | Sunday | Oct-22 | 18:00 | Calgary | Detroit | ||||
| 82 | 020081 | Completed | Sunday | Oct-22 | 21:30 | Boston | Anaheim | ||||
| 83 | 020082 | Completed | Monday | Oct-23 | 20:00 | Montreal | Buffalo | ||||
| 84 | 020083 | Completed | Tuesday | Oct-24 | 19:00 | Toronto | Washington | ||||
| 85 | 020085 | Completed | Tuesday | Oct-24 | 19:45 | Buffalo | Ottawa | ||||
| 86 | 020086 | Completed | Tuesday | Oct-24 | 20:00 | Carolina | Tampa Bay | ||||
| 87 | 020087 | Completed | Tuesday | Oct-24 | 20:00 | New Jersey | Montreal | ||||
| 88 | 020088 | Completed | Tuesday | Oct-24 | 20:30 | Dallas | Pittsburgh | ||||
| 89 | 020089 | Completed | Tuesday | Oct-24 | 20:45 | San Jose | Florida | ||||
| 90 | 020090 | Completed | Tuesday | Oct-24 | 21:00 | Colorado | NY Islanders | ||||
| 91 | 020091 | Completed | Tuesday | Oct-24 | 21:15 | Seattle | Detroit | ||||
| 92 | 020084 | Completed | Tuesday | Oct-24 | 21:30 | Anaheim | Columbus | ||||
| 93 | 020092 | Completed | Tuesday | Oct-24 | 21:45 | Boston | Chicago | ||||
| 94 | 020093 | Completed | Tuesday | Oct-24 | 21:45 | St-Louis | Winnipeg | ||||
| 95 | 020094 | Completed | Tuesday | Oct-24 | 22:00 | Edmonton | Minnesota | ||||
| 96 | 020095 | Completed | Tuesday | Oct-24 | 22:15 | Vancouver | Nashville | ||||
| 97 | 020096 | Completed | Tuesday | Oct-24 | 22:45 | NY Rangers | Calgary | ||||
| 98 | 020097 | Completed | Tuesday | Oct-24 | 23:30 | Arizona | Los Angeles | ||||
| 99 | 020098 | Completed | Tuesday | Oct-24 | 23:45 | Philadelphia | Vegas | ||||
| 100 | 020099 | Completed | Wednesday | Oct-25 | 20:30 | Washington | New Jersey | ||||
Schedule | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C100 | C2 | =TEXT([@Date],"dddd") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B2:B100 | List | =INDIRECT("StatusDrop[Status]") |
| G2:I100 | List | =INDIRECT("Teams[Teams]") |
