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
|