View Full Version : Individual Dates To Consecutive Dates

04-05-2007, 01:51 AM

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:

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.

04-05-2007, 02:10 AM
One way

04-05-2007, 02:29 AM
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.


04-10-2007, 08:03 AM
Does anyone have ideas or approaches to this problem with VBA?

04-10-2007, 09:02 AM
I have no idea how you arrive at the results looking at the inputs.

04-11-2007, 12:26 AM
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.