Extract multiple date and time values to multiple rows

Hi, WilliamV,

if Data is always laid down as in your example and in this example there is enough empty cells under the given Data:

Please Login or Register  to view this content.

Ciao,
Holger

Hi, WilliamV,

if the range is filled and I have to insert I prefer to work from the bottom up. For this example I suggested that the last line of data in K should be the start for working the way up and splitting the contents.

Please Login or Register  to view this content.

Ciao,
Holger

Holger, ok, thank you.

I would like to apply this code to included file Uitrapport.xls. You will find a file Uitrapport_filter.xls as well. This last one imports (through VBA) data from the first one. When openening it will ask to select a source file, you need to select the file Uirapport.xls. I have used VBA and formula's to obtain needed results. In column N you can find the date- and timedata. At this moment, i only use a formula to extract the time data in format from HH:MM to HH:MM, and i extend this formula manually to the available range.
I would like to apply your above code to extract dates (if multiple present) and concerning times (per date, from and till). Multiople dates imply multiple rows, as in your examples above.

Can you help me out with this? Thank you.

• UitRapport.xls
• UitRapport_filter.xls

Hi, WilliamV,

according to the workbook in Column N the header is Lange beschrijving, and there are no date and time sequences as far as the Autofillter says. Can you help me out with that, please?

Ciao,
Holger

Hi, William,

okay, I got that one. But of I look at the first line another question occurs to me.

Contents in G2 is

Please Login or Register  to view this content.

No problem for the time but a small problem concerning about the dates. To me this would mean fill up a range of dates from 05/09/12 to 12/06/13 where the weekdays meet the weekdays indicated inside the parentheses (Sun, Mon, Tue, Thur, Fri - correct me if Iīm wrong with figuring these out). So shall just be these or should all dates be added?

Ciao,
Holger

Hi, william,

I must ask you for a little patience with that macro - I didnīt expect the question to dive in that deep into my free weekend. Up to now the dates create a slight unease to me as I will have to handle both the dates as well as the weekdays - letīs see how things will develop.

Ciao,
Holger

Hi, WilliamV,

Iīm running into problems earlier to the one point where I expected them as I donīt find the consistency as I expected.

I started with the easy part, checking the first 2 letters of the string and then decide what to do.

No problem for

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but creating wrong data for

Please Login or Register  to view this content.

as the colored dates have no times and match up exactly to the pattern which is used for offsetting the array (showing 3x weekday and date).

Right now Iīm sort a helpless for finding a way to work around this as even the use of comma as a delimiter isnīt consistent throughout the data. What I may think of is comparing each item of the array and use a variable to even up to the consistency needed for the counter in the loop (Iīm no friend of mingling around with extra variables which influence the counter).

Iīll take a walk right now and think about it, maybe some fresh air will clean up my thoughts and help me with a maybe better and more suitable way to solve this problem.

Ciao,
Holger

Hi, WilliamV,

I would kindly ask you to test this limited version which I came about to write in the meantime.

Limitations are: itīs only working on the ActiveCell (that was for my testing), I left out the holidays (indicated by gesloten without a bracket in the text), I do have problems with the time span if only one is indicated at the end of the string (that should mean the time for each and any date or not?).

I know this code badly needs a tuning. Feel free to report any bugs and errors.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, WilliamV,

back again

About the inserting of two columns for not overwríte data: can be done by something like this:

Please Login or Register  to view this content.

I started putting the code to run in a loop from the bottom up - and got stuck on the very first line of data with an error. I doubt it is a very good idea to code every possible combination into the code.

As I donīt know how the data is coming to you it might be a better way to start and look at the contents of the cells and change them in a way to get a more consistent data with less exceptions to code for (that would make the code easier to maintain instead of writing new parts for any possible mistake that could appear). Like I said maybe thereīs a way to straighten up the data - depending on how the data is delivered (easiest thing would be if you would supply a template to be filled where you could influence the data to yous/my needs).

Ciao,
Holger