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

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

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

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

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