Splitting single column of dates to 2 columns representing a beginning and end date

Hi Guys,

I have 3 columns in a workbook: person id, date, and leave type, with approximately 8000 rows of data.

I would like have a macro loop through all the dates and identify when the dates appear consecutively i.e. row 1: 2013-01-01, row 2: 2013-01-02 .. etc
and in a new column insert the person id value, the start date, the end date, and the leave type for this range of dates.

The following rules have to be followed by the macro:
1. the person must be the same for each row (i.e. same integer in each cell)
2. the leave type must be the same (i.e. same integer in each cell)
3. Weekends need to be disregarded, (and in fact no dates that occur on weekends will appear in the data).

Ive attached an example workbook with the before/ after examples - which perhaps will give you a better idea of whats going on.

I had a decent crack at doing this today but after an hour or so of soul searching i decided that perhaps this could be stretching my (limited) vb programming skills.

Kind Regards
Jordan

Hi Jordan,

Look at the Pivot Table in the attached. If you read it correctly you can get your information from it. Look down the columns at consecutive days and it will show groups of star and end dates. That is the best I could do without needing VBA. I hope it makes sense to you...

OK Jordan,

After a lot of trial and error try my code. Run it on Sheet1 and step through it to see if it does what you want. It looks like this:

Please Login or Register  to view this content.

Hi Jordan,

I agree on my code. It didn't work. I still think you are going to want the total times a person took leave and of what type. I'm still thinking a Pivot Table is what you want. See the attached which has a filter for each person. See if you really don't want these numbers.