Find missing dates in date ranges with different IDs

[badlie]

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 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> </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). A B C D E 1 Start Date End Date Loc ID 01/02/2016 02/02/2016 2 01/02/2016 05/03/2016 1000 True True 3 06/02/2016 30/01/2016 1012 False False 4 08/05/2016 20/09/2016 1000 False False 5 01/08/2016 24/12/2016 1012 False False 6 25/09/2016 12/12/2016 1000 False False <tbody> </tbody> 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?

<tbody>
</tbody>

 

[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:

Loc ID

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

1000

x

x

x

x

x

x

1012

x

x

x

x

<tbody>
</tbody>

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