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
 

David,
Do all your appointments start at increments of 0:15? Or do you have
appointments with arbitrary start/end times?
For example, could you have a schedule like this?

0001 9:00 9:45
0002 9:15 9:50
0003 9:20 9:40

And if you have a schedule like this, how do you propose to encode it
in the new chart? Like this?

0001 9:00 9:15
0001 9:15 9:30
0001 9:30 9:45
0002 9:15 9:30
0002 9:30 9:45
0002 9:45 9:50
0003 9:20 9:30
0003 9:30 9:40

Kostis Vezerides

Splitting appointment records in to 15 minute time bands
 

Hi Kostis,

All the appointments do start at increments of 0:15.

Regards,
David

"vezerid" wrote:

David,
Do all your appointments start at increments of 0:15? Or do you have
appointments with arbitrary start/end times?
For example, could you have a schedule like this?

0001 9:00 9:45
0002 9:15 9:50
0003 9:20 9:40

And if you have a schedule like this, how do you propose to encode it
in the new chart? Like this?

0001 9:00 9:15
0001 9:15 9:30
0001 9:30 9:45
0002 9:15 9:30
0002 9:30 9:45
0002 9:45 9:50
0003 9:20 9:30
0003 9:30 9:40

Kostis Vezerides

Splitting appointment records in to 15 minute time bands
 

David,

It is quite hard to produce the report in the manner that you seek.
However, it is far easier to produce a histogram for the time slots as
follows:

Place the hours in 15-minute intervals in column A:A, say starting from
A2.
9:00
9:15
9:30
etc.

Next to 9:00 you use the formula:
=SUMPRODUCT((Sheet1!$A$1:$A$3<=A2)*(Sheet1!$B$1:$B $3A2))

This will now tell you how many appointments you have for each time
slot. I am assuming that if an appointment is 9:00-9:45 it will be 3
slots and the 9:45 slot will not count, as it is the end of the
appointment.

Does this help?
Kostis Vezerides


The Inquirer wrote:
Hi Kostis,

All the appointments do start at increments of 0:15.

Regards,
David

"vezerid" wrote:

David,
Do all your appointments start at increments of 0:15? Or do you have
appointments with arbitrary start/end times?
For example, could you have a schedule like this?

0001 9:00 9:45
0002 9:15 9:50
0003 9:20 9:40

And if you have a schedule like this, how do you propose to encode it
in the new chart? Like this?

0001 9:00 9:15
0001 9:15 9:30
0001 9:30 9:45
0002 9:15 9:30
0002 9:30 9:45
0002 9:45 9:50
0003 9:20 9:30
0003 9:30 9:40

Kostis Vezerides

Splitting appointment records in to 15 minute time bands
 

Thanks Kostis, much appreciated.

This worked wonderfully.

Regards,
David

"vezerid" wrote:

David,

It is quite hard to produce the report in the manner that you seek.
However, it is far easier to produce a histogram for the time slots as
follows:

Place the hours in 15-minute intervals in column A:A, say starting from
A2.
9:00
9:15
9:30
etc.

Next to 9:00 you use the formula:
=SUMPRODUCT((Sheet1!$A$1:$A$3<=A2)*(Sheet1!$B$1:$B $3A2))

This will now tell you how many appointments you have for each time
slot. I am assuming that if an appointment is 9:00-9:45 it will be 3
slots and the 9:45 slot will not count, as it is the end of the
appointment.

Does this help?
Kostis Vezerides


The Inquirer wrote:
Hi Kostis,

All the appointments do start at increments of 0:15.

Regards,
David

"vezerid" wrote:

David,
Do all your appointments start at increments of 0:15? Or do you have
appointments with arbitrary start/end times?
For example, could you have a schedule like this?

0001 9:00 9:45
0002 9:15 9:50
0003 9:20 9:40

And if you have a schedule like this, how do you propose to encode it
in the new chart? Like this?

0001 9:00 9:15
0001 9:15 9:30
0001 9:30 9:45
0002 9:15 9:30
0002 9:30 9:45
0002 9:45 9:50
0003 9:20 9:30
0003 9:30 9:40

Kostis Vezerides