Hi
The following should work ( not tested ) so work with a copy of your
data.
Be aware that if you have 30,000 rows, and each appointment takes more
than 2 time slots, then the number of rows required for your "results"
will exceed XL2003's limits of 65,536 rows.
I have amended Sandy's code to loop through your entire data set, and to
place the resulting appointments in columns
F,G and H until the number of destination rows reaches 65000, then it
jumps back to row 2 of columns J,K and L for the next set of data.
Option Explicit
Sub Split_Time()
Dim mystart As Date, myend As Date, Check As Boolean
Dim x As Long, i As Long, lr As Long, c As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 2: c = 6
For i = 2 To lr
Check = True
mystart = Format(Range("B" & i).Value, "hh:mm")
myend = Format(Range("C" & i).Value, "hh:mm")
Do
If Format(DateAdd("n", 15, mystart), "hh:mm") <= myend Then
Cells(x, c).Value = Format(Range("A" & i).Value, "00000")
Cells(x, c + 1).Value = Format(mystart, "hh:mm")
Cells(x, c + 2).Value = Format(DateAdd("n", 15, mystart), "hh:mm")
mystart = Format(DateAdd("n", 15, mystart), "hh:mm")
x = x + 1
If x > 65000 Then x = 2: c = c + 4
Else
Check = False
End If
Loop Until Check = False
Next ' i
End Sub
--
Regards
Roger Govier
The Inquirer said:
Thanks Sandy, much appreciated.
This worked a treat using cells A2 for appointment ref, B2 for start
time,
and C2 for end time, as you suggested.
My problem now is that I need amend the macro so that it runs on
subsequent
lines i.e row 1 through to the end of the list of appointments, the
number of
rows being variable but in the region of 20,000 to 30,000.
Unfortunately, I have little experience of using Excel macros so
although
you've put me on the right track I haven't a clue which way to go...
Regards,
David
Sandy said:
Not sure where all your appointment info goes but I was able to
replicate your example:
Paste this code into the sheet1 portion of a new excel file and try
it
out. In cell A2 place your appointment ref, B2 your start time, and
C2
your end time; then run the macro.
Sub Split_Time()
Dim mystart, myend As Date, Check As Boolean
Check = True
mystart = Format([B2].Value, "hh:nn")
myend = Format([C2].Value, "hh:nn")
x = 5
Do
If DateAdd("n", 15, mystart) <= myend Then
Cells(x, 1).Value = Range("A2").Value
Cells(x, 1).NumberFormat = "0000#"
Cells(x, 2).Value = mystart
Cells(x, 3).Value = Format(DateAdd("n", 15, mystart), "hh:nn")
mystart = Format(DateAdd("n", 15, mystart), "hh:nn")
x = x + 1
Else
Check = False
End If
Loop Until Check = False
End Sub
Hope this is what you were looking for or can get you on the right
track.
Sandy
The said:
I have a problem trying to split the records of my spreadsheet, an
example of
a line being: -
Appointment ref Start time End time
------------------- ----------- ----------
00001 9:00 9:45.
I want to split this in to time bands of 15 minutes i.e. the above
would
become: -
Appointment ref Start time End time
------------------- ----------- ----------
00001 9:00 9:15
00001 9:15 9:30
00001 9:30 9:45
The idea is to then count the number of appointments that occur in
each 15
minute slot i.e. my example appointment above would fall in to 3
time bands,
the 9:00, the 9:15, and the 9:30 timebands. Data will then be
charted to
demonstrate the periods of high and low activity over a 24 hour
period.
Hope someone can point me in the right direction.
Regards,
David
