Combining consecutive/overlapping date ranges

[slk]

Hi all,

Haven't been able to figure this out or find an answer. Any help would be greatly appreciated!

I have three columns: NAME, START, and END. I'm trying to find a formula/process that will combine any date ranges that are consecutive or overlap (but only for the same person). There are three ways that the END of one date range and the START of the next could be consecutive:

1.) The END might be the same as the next START.
2.) The END might be the day before the next START.
3.) The END might be before the next START (there might be an overlap).

Of course, if the ranges aren't consecutive or overlapping, nothing should be done.

For example, if I start with:

NAME              START       END
JOHN DOE          7/3/2008    7/10/2008
JOHN DOE          7/10/2008   7/14/2008
JOHN DOE          8/9/2008    8/20/2008
BOB SMITH         2/4/2002    2/18/2002
BOB SMITH         2/19/2002   2/21/2002
BOB SMITH         2/22/2002   3/1/2002
BILL JOHNSON      11/3/2005   11/9/2005
BILL JOHNSON      11/7/2005   11/11/2005
BILL JOHNSON      11/10/2005  11/20/2005
JANE WILLIAMS     11/21/2005  12/1/2005

This is what I'm trying to get as the result:

NAME              START        END
JOHN DOE          7/3/2008     7/14/2008
JOHN DOE          8/9/2008     8/20/2008
BOB SMITH         2/4/2002     3/1/2002
BILL JOHNSON      11/3/2005    11/20/2005
JANE WILLIAMS     11/21/2005   12/1/2005

Does anyone have any ideas? Thanks!

 

[AlphaFrog]

Hi and welcome to the board.

Give this a try. The macro assumes your data is in columns A, B, and C. And places the consolidated results below your data.

Sub Consolidate_Dates()
    
    Dim cell As Range
    Dim Nextrow As Long
    Dim Startdate As Date
    
    Nextrow = Range("A" & Rows.Count).End(xlUp).Row + 2
    Startdate = Range("B2").Value
    
    Application.ScreenUpdating = False
    For Each cell In Range("A2", Range("A2").End(xlDown))
        If cell.Value <> cell.Offset(1).Value Or _
           cell.Offset(0, 2).Value < cell.Offset(1, 1).Value - 1 Then
            Range("A" & Nextrow).Resize(1, 3).Value = cell.Resize(1, 3).Value
            Range("B" & Nextrow).Value = Startdate
            Nextrow = Nextrow + 1
            Startdate = cell.Offset(1, 1).Value
        End If
    Next cell
    Application.ScreenUpdating = True
    
End Sub

 

[slk]

Thanks for the reply--I'll give this a try on Monday.

Would it be hard to modify this to work if you had, say, 3 other columns before and the NAME, START, and END columns were D, E, and F?

 

[AlphaFrog]

Thanks for the reply--I'll give this a try on Monday.

Would it be hard to modify this to work if you had, say, 3 other columns before and the NAME, START, and END columns were D, E, and F?

Change all the A and B references within the code to D and E

 

[slk]

This worked perfectly. Thanks!

 

[Granorc]

Hi
I do have a huge list of employees and I like to find every persons overlapping records.
And this record should be marked (or counted) in col D

Employee 10541 is OK, employee 10577 should have a mark for overlap in col D etc.
Employee 10602 also have a couple of overlapping days that should be marked (or counted) in col D

would this be possible?

Empl	Start	End
10541	18.12.2013	23.12.2013
10541	12.02.2014	14.02.2014
10541	18.12.2014	19.12.2014
10541	21.01.2015	02.02.2015
10577	01.02.2015	05.02.2015
10577	19.01.2015	06.02.2015
10602	14.02.2014	31.02.2014
10602	28.02.2014	02.05.2014
10602	05.01.2015	06.01.2015
10602	07.05.2015	07.05.2015
10633	25.01.2015	29.01.2015
10666	26.07.2013	10.07.2014
10666	16.09.2014	16.09.2014
10666	29.12.2014	30.12.2014
10666	09.03.2015	01.04.2015

<colgroup><col width="80" span="3" style="width:60pt"> </colgroup><tbody>
</tbody>