Hi,
I want to determine if there's a gap between dates for each week and team.
For example, Team A on week 1 has dates of 1/30 & 1/31 so there is no gap between the dates.
Team A on week 2 has dates of 2/6, 2/7 & 2/9 creating a gap between 2/7 & 2/9.
The dates in the column may not run chronological order
I thought I could used something like SUMIFs or a combination of Sumifs and Countif but not sure how to work in if a date in a range is greater than 1 day.
Below is an example with results.
Any advice/help is appreciated.
I want to determine if there's a gap between dates for each week and team.
For example, Team A on week 1 has dates of 1/30 & 1/31 so there is no gap between the dates.
Team A on week 2 has dates of 2/6, 2/7 & 2/9 creating a gap between 2/7 & 2/9.
The dates in the column may not run chronological order
I thought I could used something like SUMIFs or a combination of Sumifs and Countif but not sure how to work in if a date in a range is greater than 1 day.
Below is an example with results.
Any advice/help is appreciated.
Travel.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | date | Week# | Team | Results | ||||||
2 | 1/30/24 | 1 | A | Is there a gap? | ||||||
3 | 1/31/24 | 1 | A | Team | Week # | |||||
4 | 2/1/24 | 1 | B | 1 | 2 | 3 | ||||
5 | 2/6/24 | 2 | A | A | NO | YES | NO | |||
6 | 2/6/24 | 2 | C | B | NO | NO | NO | |||
7 | 2/7/24 | 2 | A | C | NO | YES | YES | |||
8 | 2/7/24 | 2 | C | |||||||
9 | 2/8/24 | 2 | B | 90643 | ||||||
10 | 2/9/24 | 2 | A | |||||||
11 | 2/9/24 | 2 | B | |||||||
12 | 2/9/24 | 2 | C | |||||||
13 | 2/12/24 | 3 | C | |||||||
14 | 2/13/24 | 3 | C | |||||||
15 | 2/15/24 | 3 | C | |||||||
16 | 2/16/24 | 3 | C | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E9 | E9 | =SUMIFS(A2:A16,B2:B16,F4,C2:C16,E5) |
B2:B16 | B2 | =INT((A2-DATE(2024,1,29))/7)+(WEEKDAY(A2)<7) |