Filter - Date interval

[Metty]

Hello,

My case is as following:

I have a database of event participants with their respective participation period (start date and end date) as in below example:

Name Start Date End Date

Julie 11/03/2019 24/04/2019

Tom 25/03/2019 15/04/2019

Jeff 05/04/2019 23/05/2019

Brad 12/05/2019 19/06/2019

John 24/04/2019 10/06/2019

I need a function/formula/command which would let me filter participants by date range for finding answers for below questions:

- Who participated during the time period of, for example, 25/05/2019-05/06/2019?
- How many participants were there in, for example, April?
- To see the big picture on participation level by month/date range in chart.

The methods that I tried allow only filtering by either start date or end date, so I cannot use both of them for formulating an interval.

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F	G	H
1					25/05/2019	Attended		Count
2	Julie	11/03/2019	24/04/2019		05/06/2019	Brad	01/04/2019	4
3	Tom	25/03/2019	15/04/2019			John
4	Jeff	05/04/2019	23/05/2019
5	Brad	12/05/2019	19/06/2019
6	John	24/04/2019	10/06/2019
7
8
Lookup

Cell Formulas
Range	Formula
H2	=COUNTIFS(B2:B6,"<="&EDATE(G2,1),C2:C6,">="&G2)
F2	=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(($B$2:$B$6<=$E$1)*($C$2:$C$6>=$E$2)),ROWS(F$2:F2))),"")

 

[Metty]

Hi Fluff,

Thank you for your response.

Maybe you have an idea about how I could visualize it as we normally do in timeline in pivottable? Would it be possible in my case?

 

[Fluff]

I've never used pivots, so cannot help with that.