Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using time formats in minute units | Excel Discussion (Misc queries) | |||
Time Sheets | New Users to Excel | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
unmet challenge | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |