adjusting formula get data between two dates

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
921
Office Version
  1. 2010
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.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,181
Office Version
  1. 2016
Platform
  1. Windows
How about
...
=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)), "")
...

@Fluff It's very possible I'm missing something but wouldn't this need to be entered as an array formula using CSE?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Oops, keep forgetting to do that.
@abdelfattah
As mentioned by Toadstool, the formula should be confirmed with Ctrl Shift Enter, not just enter.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,257
Messages
5,577,016
Members
412,761
Latest member
McCrab
Top