check for startdate and enddate with input startdate and enddate then pull the data.

[rockyhawkky]

Hi there, Here is my example, I got a chunk of data in sheet1 here, it's a task list contain start and end dates for each task. (it's comes from my calendar as power querry so it may fill up any times)

My Scheduel.xlsx
	A	B	C	D
1	Start	End	My Task	Priority
2	22/10/2022	25/10/2022	Task 1	LOW
3	23/10/2022	24/10/2022	Task 2	LOW
4	24/10/2022	25/10/2022	Task 3	LOW
5	25/10/2022	28/10/2022	Task 4	LOW
6	26/10/2022	28/10/2022	Task 5	LOW
7	28/10/2022	28/10/2022	Task 6	HIGH
8	24/10/2022	27/10/2022	Task 7	LOW
9	19/10/2022	25/10/2022	Task 8	LOW
10	21/10/2022	30/10/2022	Task 9	LOW
11	26/10/2022	31/10/2022	Task 10	LOW
Sheet1

and i have sheet2 it's has a input as select week.

My Scheduel.xlsx
	A	B	C	D	E	F
1	Start	End	My Task This Week	Priority	WeekStart	WeekEnd
2					22/10/2022	28/10/2022
Sheet2

Cell Formulas
Range	Formula
F2	F2	=E2+6

So I want to pull data that is in range of my selected start(E2) and end(F2) week to display in sheet2 and also want to pull the data that is past my selected week too. in this case, as you can see task8,task9,task10 it start or end dates are in another week but it has an activity going on in selected week so I also want to display them in sheet2.

Any help will be appreciated, Thank you.

 

[bebo021999]

Book2
	A	B	C	D	E	F
1	Start	End	My Task This Week	Priority	WeekStart	WeekEnd
2	19/10/2022	25/10/2022	Task 8	LOW	22/10/2022	28/10/2022
3	21/10/2022	30/10/2022	Task 9	LOW
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sheet2

Cell Formulas
Range	Formula
A2:D32	A2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$11)/(Sheet1!$A$2:$A$11<$E$2)/(Sheet1!$B$2:$B$11>=$E$2),ROWS($1:1))),"")
F2	F2	=E2+6

 

[rockyhawkky]

@bebo021999
thank for the idea
sorry i mean "passed" not "past" and my table was wrong.

here is correct table for demonstrate

Book2.xlsx
	A	B	C	D
1	Start	End	My Task	Priority
2	22/10/2022	25/10/2022	Task 1	LOW
3	18/10/2022	20/10/2022	Task 2	LOW
4	24/10/2022	25/10/2022	Task 3	LOW
5	29/10/2022	31/10/2022	Task 4	LOW
6	26/10/2022	28/10/2022	Task 5	LOW
7	28/10/2022	28/10/2022	Task 6	HIGH
8	24/10/2022	27/10/2022	Task 7	LOW
9	19/10/2022	25/10/2022	Task 8	LOW
10	21/10/2022	30/10/2022	Task 9	LOW
11	26/10/2022	31/10/2022	Task 10	LOW
Sheet1

just wan't to pull data that match any of case A or B or C or D
case E or F are not in period of select week.

or by reverse just pull everything that doesn't match E or F case
with new table above task2 and task4 are not in period of select week my result should be like this

Book2.xlsx
	A	B	C	D	E	F
1	Start	End	My Task This Week	Priority	WeekStart	WeekEnd
2	22/10/2022	25/10/2022	Task 1	LOW	22/10/2022	28/10/2022
3	24/10/2022	25/10/2022	Task 3	LOW
4	26/10/2022	28/10/2022	Task 5	LOW
5	28/10/2022	28/10/2022	Task 6	HIGH
6	24/10/2022	27/10/2022	Task 7	LOW
7	19/10/2022	25/10/2022	Task 8	LOW
8	21/10/2022	30/10/2022	Task 9	LOW
9	26/10/2022	31/10/2022	Task 10	LOW
Sheet2

Cell Formulas
Range	Formula
F2	F2	=E2+6

 

[bebo021999]

Again:

Book1
	A	B	C	D	E	F
1	Start	End	My Task This Week	Priority	WeekStart	WeekEnd
2	22/10/2022	25/10/2022	Task 1	LOW	22/10/2022	28/10/2022
3	24/10/2022	25/10/2022	Task 3	LOW
4	26/10/2022	28/10/2022	Task 5	LOW
5	28/10/2022	28/10/2022	Task 6	HIGH
6	24/10/2022	27/10/2022	Task 7	LOW
7	19/10/2022	25/10/2022	Task 8	LOW
8	21/10/2022	30/10/2022	Task 9	LOW
9	26/10/2022	31/10/2022	Task 10	LOW
Sheet2

Cell Formulas
Range	Formula
A2:D9	A2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$11)/((Sheet1!$A$2:$A$11>$F$2)+(Sheet1!$B$2:$B$11<$E$2)=0),ROWS($1:1))),"")
F2	F2	=E2+6

 

[rockyhawkky]

@bebo021999 YOU'RE A GOD