adjusting formula get data between two dates

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
909
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,161
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,787
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
50,787
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,602
Messages
5,573,158
Members
412,508
Latest member
Sarge24
Top