# adjusting formula get data between two dates

#### abdelfattah

##### Well-known Member
hello i have this formula works based on year, i would amend between two dates the result should be in column e based on column a after writting start date and end date in range e1,g1

1 (1).xlsx
ABCDEFG
1studentdatestart dateend date
2student12019/01/01
3student22019/03/01student
4student32019/05/01
5student42019/07/01
6student52019/09/01
7student12019/11/01
8student22020/01/01
9student32020/03/01
10student42020/05/01
11student52020/07/01
12student112020/09/01
13student122020/11/01
14student132021/01/01
15student142021/03/01
16student152021/05/01
17student162021/07/01
18student172021/09/01
19student182021/11/01
20student192022/01/01
21student202022/03/01
22student212022/05/01
23student222022/07/01
24
Sheet1
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX(\$A\$2:\$A\$170, MATCH(0,COUNTIF(\$E\$3:E3, IF((\$E\$1=YEAR(\$B\$2:\$B\$170)), \$A\$2:\$A\$170, \$E\$3)), 0)), "")
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

##### Well-known Member
Hi AbdelFatta,

This should do what you want while avoiding an array formula.

AbdetFattah-P2.xlsx
ABCDEFG
1studentdateStart date2/2/2020End date2/1/2021
2student11/1/2019
3student23/1/2019student
4student35/1/2019student3
5student47/1/2019student4
6student59/1/2019student5
7student111/1/2019student11
8student21/1/2020student12
9student33/1/2020student13
10student45/1/2020
11student57/1/2020
12student119/1/2020
13student1211/1/2020
14student131/1/2021
15student143/1/2021
16student155/1/2021
17student167/1/2021
18student179/1/2021
19student1811/1/2021
20student191/1/2022
21student203/1/2022
22student215/1/2022
23student227/1/2022
Sheet1
Cell Formulas
RangeFormula
E4:E23E4=IFERROR(INDEX(\$A\$2:\$A\$23,AGGREGATE(15,6,ROW(\$A\$2:\$A\$23)-ROW(\$A\$1)/((\$B\$2:\$B\$23>=\$E\$1)*(\$B\$2:\$B\$23<=\$G\$1)),ROW()-ROW(\$E\$3))),"")

#### abdelfattah

##### Well-known Member
many thanks it works very well

#### abdelfattah

##### Well-known Member
hi, toad i would amend the formula if you don't mind i want duplicated data highlighted by yellow color should deleted when search by two dates

##### Well-known Member

You can extract into an unused column (maybe hide it?) then de-duplicate into your target area:

AbdetFattah-P2.xlsx
ABCDEFGH
1studentdateStart date2019/1/1End date2020/5/1
2student12019/1/1
3student22019/3/1studentExtract
4student32019/5/1student1student1
5student42019/7/1student2student2
6student52019/9/1student3student3
7student12019/11/1student4student4
8student22020/1/1student5student5
9student32020/3/1 student1
10student42020/5/1 student2
11student52020/7/1 student3
12student112020/9/1 student4
13student122020/11/1
14student132021/1/1
15student142021/3/1
16student152021/5/1
17student162021/7/1
18student172021/9/1
19student182021/11/1
20student192022/1/1
21student202022/3/1
22student212022/5/1
23student222022/7/1
v2 (2)
Cell Formulas
RangeFormula
E4:E23E4=IFERROR(INDEX(\$H\$4:\$H\$23,MATCH(0,INDEX(COUNTIF(\$E\$3:\$E3,\$H\$4:\$H\$23),),0))&"","")
H4:H23H4=IFERROR(INDEX(\$A\$2:\$A\$23,AGGREGATE(15,6,ROW(\$A\$2:\$A\$23)-ROW(\$A\$1)/((\$B\$2:\$B\$23>=\$E\$1)*(\$B\$2:\$B\$23<=\$G\$1)),ROW()-ROW(\$H\$3))),"")

#### abdelfattah

##### Well-known Member
hi, toad it works but i prefer do that without in depend on specific column like h as in this case if is way directly

#### Fluff

##### MrExcel MVP, Moderator

+Fluff New.xlsm
ABCDEFG
1studentdatestart date01/01/2019end date31/12/2020
2student101/01/2019
3student203/01/2019student
4student305/01/2019student1
5student407/01/2019student2
6student509/01/2019student3
7student111/01/2019student4
8student201/01/2020student5
9student303/01/2020student11
10student405/01/2020student12
11student507/01/2020
12student1109/01/2020
13student1211/01/2020
14student1301/01/2021
15student1403/01/2021
16student1505/01/2021
17student1607/01/2021
18student1709/01/2021
19student1811/01/2021
20student1901/01/2022
21student2003/01/2022
22student2105/01/2022
23student2207/01/2022
24
25
Main
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX(\$A\$2:\$A\$170, MATCH(0,COUNTIF(\$E\$3:E3, \$A\$2:\$A\$170)+(\$B\$2:\$B\$170<\$E\$1)+(\$B\$2:\$B\$170>\$G\$1), 0)), "")

#### abdelfattah

##### Well-known Member
it gives me empty no data

#### abdelfattah

##### Well-known Member
إ (1).xlsx
ABCDEFG
1studentdatestart date01/01/2019end date05/01/2020
2student101/01/2019
3student203/01/2019student
4student305/01/2019
5student407/01/2019
6student509/01/2019
7student111/01/2019
8student201/01/2020
9student303/01/2020
10student405/01/2020
11student507/01/2020
12student1109/01/2020
13student1211/01/2020
14student1301/01/2021
15student1403/01/2021
16student1505/01/2021
17student1607/01/2021
18student1709/01/2021
19student1811/01/2021
20student1901/01/2022
21student2003/01/2022
22student2105/01/2022
23student2207/01/2022
24
25
26
Sheet1
Cell Formulas
RangeFormula
E4:E24E4=IFERROR(INDEX(\$A\$2:\$A\$170,MATCH(0,COUNTIF(\$E\$3:E3, \$A\$2:\$A\$170)+(\$B\$2:\$B\$170<\$E\$1)+(\$B\$2:\$B\$170>\$G\$1), 0)), "")

Replies
7
Views
161
Replies
2
Views
110
Replies
3
Views
111
Replies
17
Views
636
Replies
2
Views
70