Find missing dates in date ranges with different IDs

[badlie]

Loc ID	Start Date	End Date
1000	01/02/2016	05/03/2016
1000	25/09/2016	12/12/2016
1012	01/08/2016	24/12/2016
1000	08/05/2016	20/09/2016
1012	06/02/2016	30/07/2016
1000	01/02/2016	05/03/2016

<tbody>
[TD="class: votecell"][/TD]
[TD="class: postcell"]
I need to find out which dates are missing within multiple date ranges. All the solutions I find online are for just a simple date range for only one unique ID, e.g. Sample 1 .
I have a worksheet with +200k lines of data. Each line has a location ID and a start and end date. Each identifier has multiple sets of start & end date ranges and these can potentially be repetitive and out of order. I need to find the missing dates for each location identifier separately. I have found this question which is very similar to mine but I cannot make it work for me: Find missing dates in a range of dates in Excel
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]

</tbody>

I can imagine the results can be displayed in various ways. For example, a 12-column table could show if there is a missing day/s in any month within a year for each identifier. Or a table showing missing dates for each identifier in a text string.
At the moment, this is what I have done which I find very inefficient:

I have pivoted the original data to get unique values from these three columns together. I have then made a table next to it with individual days as column headings. Each cell in this table is a binary calculated to show whether that individual day is within the range using

<code>=D$2=MEDIAN(D$2,$A2,$B2)</code>

and then pasting the results as value (otherwise excel cannot seem to handle so many formulas).
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Loc ID
[/TD]
[TD]01/02/2016
[/TD]
[TD]02/02/2016
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01/02/2016
[/TD]
[TD]05/03/2016
[/TD]
[TD]1000
[/TD]
[TD]True
[/TD]
[TD]True
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]06/02/2016
[/TD]
[TD]30/01/2016
[/TD]
[TD]1012
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]08/05/2016
[/TD]
[TD]20/09/2016
[/TD]
[TD]1000
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]01/08/2016
[/TD]
[TD]24/12/2016
[/TD]
[TD]1012
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]25/09/2016
[/TD]
[TD]12/12/2016
[/TD]
[TD]1000
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
</tbody>[/TABLE]

There are +18k lines in this pivot and I have at least 700+ columns for individual dates which is growing. It is not a solution because the data is being added every month and the pivot table changes. Because the binary data is pasted as value I have to start from scratch when this happens.
Could someone suggest an efficient, expandable, and updateable method please? VBA?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

You might get helpful responses if you would define how you determine what a missing day/date is. ie. Does it mean a blank cell in either the 'Start Date' or 'End Date' columns, or is it something you calculate by using the 'Start Date' and 'End Date' criteria? I have tried to glean the objective from the current information and cannot reach a logical conclusion. But then, I am not the brightest star in the sky.

 

[badlie]

Does it mean a blank cell in either the 'Start Date' or 'End Date' columns, or is it something you calculate by using the 'Start Date' and 'End Date' criteria? I have tried to glean the objective from the current information and cannot reach a logical conclusion.

You make a very valid point,I was hasty in my post and didn't include everything. Cannot find where to edit it though. LOL

The data in 'Start Date' and 'End Date' is always populated. I need to find out the time ranges not covered by these entries for every ID; e.g., for the example above I would like to know that for ID 1000 (between 01/01/2016 and 31/12/2016) 01/01/2016-31/01/2016, 06/03/2016-07/05/2016, 21/09/2016-24/09/2016, and 13/12/2016-31/12/2016 are missing. The format I currently have is like below, which is simplified to just show months with any missing days:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Loc ID[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

It'd be great to be able to get specific dates as well, but even a simplified table would be enough. I am open to other ways of displaying the information of course. Thanks.

 

[JLGWhiz]

OK, thanks for the explanation, but I am going to pass on this one. My lack of skills with dates would take more research and time than I want to put into it at this point. Perhaps someone else more adept will pick up on the thread. If not, just start a new thread tomorrow and include the clarification of what you want in it.

 

[MickG]

Try this, for Results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG02Mar03
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Q [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, nnDt  [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Sheets("Sheet2").Cells.ClearContents
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         [COLOR="Navy"]For[/COLOR] Dt = Dn.Offset(, 1) To Dn.Offset(, 2)
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dt) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dt), Nothing
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dt
    [COLOR="Navy"]Next[/COLOR] Dn
   
 c = 1
 Const nDt [COLOR="Navy"]As[/COLOR] Date = "31/12/2015"
ReDim ray(1 To Dic.Count + 1, 1 To 367)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
    c = c + 1
    ray(1, 1) = "Loc ID"
       ray(c, 1) = k
        [COLOR="Navy"]For[/COLOR] n = 1 To 366
            nnDt = DateAdd("d", n, nDt)
            ray(1, n + 1) = nnDt
            [COLOR="Navy"]If[/COLOR] Not Dic(k).exists(nnDt) [COLOR="Navy"]Then[/COLOR]
                ray(c, n + 1) = "Missing"
            [COLOR="Navy"]Else[/COLOR]
                ray(c, n + 1) = ""
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(Dic.Count + 1, 366)
    .Value = ray
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick