Splitting appointment records in to 15 minute time bands
 

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

Splitting appointment records in to 15 minute time bands
 

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 Inquirer wrote:
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

Splitting appointment records in to 15 minute time bands
 

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" wrote:

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 Inquirer wrote:
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

Splitting appointment records in to 15 minute time bands
 

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" wrote in message
...
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" wrote:

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 Inquirer wrote:
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