Individual Dates To Consecutive Dates

[nk18]

Hello,

Any ideas on how to achieve the following: I have a list of names and individual dates and what I would like to do is convert them to a 'names' column, a 'to' column and a 'from' column where the the 'to' and 'from' columns are the dates that are consecutive weekdays (weekends aren't included so a Friday & Monday would count as consecutive). Here is some sample data:

Original:
Name Date
Olivia 03-Jan-06
Clare 05-Jan-06
Clare 06-Jan-06
Stuart 06-Jan-06
Stuart 09-Jan-06
Stuart 10-Jan-06

Converted to:
Name To From
Olivia 03-Jan-06 03-Jan-06
Clare 05-Jan-06 06-Jan-06
Stuart 06-Jan-06 10-Jan-06

Thanks for any help.

[Bob Phillips]

One way

[nk18]

Thanks for the reply and file xld. Sorry to bother you again, but I didn't supply enough data/information last time: I'm trying record each instance of consecutive days not just a summary for each person e.g.

Stuart's summary could be:

Stuart 06-Jan-06 10-Jan-06
Stuart 03-Mar-06 03-Mar-06
Stuart 28-Dec-06 04-Jan-07

Ideally in VBA, but not essentially.

nk18

[nk18]

Does anyone have ideas or approaches to this problem with VBA?

[Bob Phillips]

I have no idea how you arrive at the results looking at the inputs.

[nk18]

Hi xld,

Thanks for the response anyway!

In case anyone else can help: Stuart's summary in post #3 would have started with:

Name Date Day
Stuart 06-Jan-06 Fri
Stuart 09-Jan-06 Mon
Stuart 10-Jan-06 Tue
Stuart 03-Mar-06 Fri
Stuart 28-Dec-06 Thu
Stuart 29-Dec-06 Fri
Stuart 02-Jan-07 Tue
Stuart 03-Jan-07 Wed
Stuart 04-Jan-07 Thur

(i.e. contains no weekends or holidays)...and would end up as summarised in post #3.