Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 156
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using time formats in minute units josh Excel Discussion (Misc queries) 4 September 11th 05 10:19 PM
Time Sheets smiller3128 New Users to Excel 1 August 4th 05 08:17 PM
convert time from 60 minute hour to 100 minute hour Jboerding Excel Discussion (Misc queries) 2 July 6th 05 11:30 PM
unmet challenge boris Excel Worksheet Functions 2 March 16th 05 03:13 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM


All times are GMT +1. The time now is 12:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"