+ Reply to Thread
Results 1 to 2 of 2

Overlapping Dates, Gaps in Dates, Double Counting

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Overlapping Dates, Gaps in Dates, Double Counting

    Hello Everyone,

    I'm having a data file with around 15000 rows of data. I have, say 3 columns, Personal ID, Start date and Stop date. There are several events for a particular Personal ID.

    Data looks like:

    id startdate enddate
    1 13-Mar-2012 9-Apr-2012
    1 12-Apr-2012 7-May-2012
    1 8-May-2012 4-Jun-2012
    1 5-Jun-2012 29-Jun-2012
    1 30-Jun-2012 30-Jul-2012
    2 16-Sep-2012 29-Sep-2012
    2 30-Sep-2012 13-Oct-2012
    2 14-Oct-2012 10-Nov-2012
    2 11-Nov-2012 8-Dec-2012
    2 9-Dec-2012 5-Jan-2013
    3 24-Jul-2012 20-Aug-2012
    3 21-Aug-2012 17-Sep-2012
    3 18-Sep-2012 15-Oct-2012
    3 16-Oct-2012 12-Nov-2012
    3 13-Nov-2012 21-Nov-2012

    I need to find out whether there are any
    1. Overlapping date ranges? or any
    2. Gaps (missing dates) between an Initial start date and Last stop date? for a particular Personal ID.

    I was searching for a solution since so long and found out at one forum the solution for this. The attached file gives desired out put and as expected (only limited to the data present in that file).

    I copy pasted data (sorted by ID, then by start date, then by end date) from my worksheet into the file provided, I defined the "Data_Range" into Excel as per my data (~ 15000 rows) and ran the macro but the output has not all the Overlapping dates and Gaps. Output is improper when considering large data..

    Please help me out at this stage..if I'm missing out anything or if I need to perform any task at some point like formatting, specifying any range or do I need to adapt the code?

    or can anyone please provide a better approach for the same?

    Please note: There are around 15000 rows. and the numbers keep on varying company to company.

    I have seen that the code does not take the ID into account in deciding the missing dates ; also it does not detect overlapping dates between IDs.
    So, You can ignore the second case where Overlapping dates between IDs is concerned; not required.

    But the first case is mandatory; i.e. code must take ID into account in deciding the missing dates.

    To make it simple, Both Overlapping dates and Missing dates should be Per ID, Not between IDs.

    Thanks
    -Arun
    Attached Files Attached Files
    Last edited by arunkushvaha; 04-11-2013 at 10:32 AM. Reason: To Provide More Precise Information

  2. #2
    Registered User
    Join Date
    04-02-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Overlapping Dates, Gaps in Dates, Double Counting

    Solved it!! using SQL in MS Access.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1