Have date ranges. Given a bigger date range, I need to identify gaps.

[James Keuning]

I have data that looks like this:

Name	MediaID	StartDate	EndDate
Mike	00001	02/01/18	10/31/18
Mike	00001	05/01/18	09/30/18
Lisa	00001	05/01/17	02/28/18
Lisa	00002	01/01/19	05/31/19
Lisa	00003	02/01/16	10/31/16

I get questions that I need to answer like this (I do not know if these answers are correct, this is just an example, but even mocking this up just emphasized why I need a solution):

Question 1:
I need data for Mike from 1/1/18-12/31/19
Answer 1:
I need to get data for 1/1/18-1/31/18 and 11/1/18-12/31/19

Question 2:
I need data for Lisa for 1/1/17 - present
Answer 2:
I need to get data for 1/1/17-4/31/17 and 3/1/18-12/31/18 and 6/1/19-present

I am pursuing a solution in Access also. I am pretty much looking for any shortcuts to get this done, I have no idea where to start.

EDIT: all of the dates will be within the last ten years
EDIT2: added sample data

[Pepe Le Mokko]

Please read the yellow banner

[James Keuning]

done; thanks

[JeteMc]

First of all let me say that I believe this is going to be pretty resource intense.
In the attached file the green shaded cells will need to be filled manually with the name, first date for which data needs to be gathered and last date for which data needs to be gathered.
The cells in I2:I750 show all of the "gap" dates and are populated with the following amazing array entered formula* (which I stole from another contributor about four years ago, I have forgotten whom so I apologize for not giving due credit):

Please Login or Register  to view this content.

Cells J2:J750 identify the start (1) and end (2) of each range of dates and are populated using: =IF(ISERR(I2),"",IF(ISERR(I3),2,IF(I2>SUM(I1,1),1,IF(SUM(I2,1)< I3,2,""))))
Cells F2:G6 show the "non gap" dates for the person and data range using: =IFERROR(INDEX(C$2:C$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$1))/($A$2:$A$6=$E$1)/($D$2:$D$6>=$F$1),ROWS($A$1:$A1))),"")
Cells F8:G11 (shaded blue) displays the output ranges using: =IFERROR(INDEX($I$2:$I$750,AGGREGATE(15,6,(ROW($I$2:$I$750)-ROW($I$1))/($J$2:$J$750=COLUMNS($A$1:A$1)),ROWS($A$1:$A1))),"")
Note that columns I:J may be moved and or hidden for aesthetic purposes.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.