I work for an ambulance service. I wish to track, via a horizontal bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4 digits
for time without dropping leading zero), ie A1=0600, B1=0601, C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1, A3=Medic 2, etc

Somewhere in the spreadsheet, I have a section that I enter the dispatch
times and the end time of each call. Lets say column BA1:BA10 equals start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times 0600 - 1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately the
timeslot for each call. So if Medic 1 was on a call from 0605 to 0700, the
cells for Medic 1 under those times would fill in. I know this will be a
conditional formatting, but not sure how to have those cells reference the
start/stop array in order to know whether to fill in or not. Any cells that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the color fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....| 0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les

Hi!

What you want to do is called a Gantt chart. Basically, it's fairly simple
to do, however, you have a couple of problems!

If you want to chart the time range from 6:00 AM to 18:00 in increments of 1
minute you will run out of cells (in a row - A1:IV1) at 10:15 AM. There are
only 256 columns and you have 720 minutes in your time range.

Another issue is using TEXT to represent the times. It would be *MUCH*
easier to use normal time entries (any format).

Another issue, and this will be the most difficult to deal with, is, how
many times will a unit be dispatched? If a unit will only be dispatched once
then it's really simple, no problem! If a unit might be dispatched 10 times,
ugh!

The basic logic is to compare the the TIME in row 1 to the start and stop
times of the dispatched unit.

Let me know how (if) you want to procede.

Biff

"WLMPilot" wrote in message
...
I work for an ambulance service. I wish to track, via a horizontal bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4
digits
for time without dropping leading zero), ie A1=0600, B1=0601, C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1, A3=Medic 2,
etc

Somewhere in the spreadsheet, I have a section that I enter the dispatch
times and the end time of each call. Lets say column BA1:BA10 equals
start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times 0600 -
1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately the
timeslot for each call. So if Medic 1 was on a call from 0605 to 0700,
the
cells for Medic 1 under those times would fill in. I know this will be a
conditional formatting, but not sure how to have those cells reference the
start/stop array in order to know whether to fill in or not. Any cells
that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the color
fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....| 0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les

I want to continue and yes, I understand the limitations of the cells and the
problem with the same ambulance being dispatch several times. I want to
proceed and figure this out. The time indications I will adjust to 5 or 10
min increments. The basic problem is that I want compare time header and see
if it falls in any of the ranges of the start/stop times. If so, then fill
that cell with a color thus creating a bar graph.

"Biff" wrote:

Hi!

What you want to do is called a Gantt chart. Basically, it's fairly simple
to do, however, you have a couple of problems!

If you want to chart the time range from 6:00 AM to 18:00 in increments of 1
minute you will run out of cells (in a row - A1:IV1) at 10:15 AM. There are
only 256 columns and you have 720 minutes in your time range.

Another issue is using TEXT to represent the times. It would be *MUCH*
easier to use normal time entries (any format).

Another issue, and this will be the most difficult to deal with, is, how
many times will a unit be dispatched? If a unit will only be dispatched once
then it's really simple, no problem! If a unit might be dispatched 10 times,
ugh!

The basic logic is to compare the the TIME in row 1 to the start and stop
times of the dispatched unit.

Let me know how (if) you want to procede.

Biff

"WLMPilot" wrote in message
...
I work for an ambulance service. I wish to track, via a horizontal bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4
digits
for time without dropping leading zero), ie A1=0600, B1=0601, C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1, A3=Medic 2,
etc

Somewhere in the spreadsheet, I have a section that I enter the dispatch
times and the end time of each call. Lets say column BA1:BA10 equals
start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times 0600 -
1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately the
timeslot for each call. So if Medic 1 was on a call from 0605 to 0700,
the
cells for Medic 1 under those times would fill in. I know this will be a
conditional formatting, but not sure how to have those cells reference the
start/stop array in order to know whether to fill in or not. Any cells
that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the color
fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....| 0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les

Hi!

If you're still checking this thread contact me at:

xl can help at comcast period net

Remove the can and change the obvious.

This can be done but I'll bet it's not going to "work" like you think.

The multiple dispatches of a particular unit can be dealt with but it's a
somewhat involved process.

Biff

"WLMPilot" wrote in message
...
I want to continue and yes, I understand the limitations of the cells and
the
problem with the same ambulance being dispatch several times. I want to
proceed and figure this out. The time indications I will adjust to 5 or
10
min increments. The basic problem is that I want compare time header and
see
if it falls in any of the ranges of the start/stop times. If so, then
fill
that cell with a color thus creating a bar graph.

"Biff" wrote:

Hi!

What you want to do is called a Gantt chart. Basically, it's fairly
simple
to do, however, you have a couple of problems!

If you want to chart the time range from 6:00 AM to 18:00 in increments
of 1
minute you will run out of cells (in a row - A1:IV1) at 10:15 AM. There
are
only 256 columns and you have 720 minutes in your time range.

Another issue is using TEXT to represent the times. It would be *MUCH*
easier to use normal time entries (any format).

Another issue, and this will be the most difficult to deal with, is, how
many times will a unit be dispatched? If a unit will only be dispatched
once
then it's really simple, no problem! If a unit might be dispatched 10
times,
ugh!

The basic logic is to compare the the TIME in row 1 to the start and stop
times of the dispatched unit.

Let me know how (if) you want to procede.

Biff

"WLMPilot" wrote in message
...
I work for an ambulance service. I wish to track, via a horizontal bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4
digits
for time without dropping leading zero), ie A1=0600, B1=0601, C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than
A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1, A3=Medic
2,
etc

Somewhere in the spreadsheet, I have a section that I enter the
dispatch
times and the end time of each call. Lets say column BA1:BA10 equals
start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times 0600 -
1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately the
timeslot for each call. So if Medic 1 was on a call from 0605 to 0700,
the
cells for Medic 1 under those times would fill in. I know this will be
a
conditional formatting, but not sure how to have those cells reference
the
start/stop array in order to know whether to fill in or not. Any cells
that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the color
fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....|
0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les

I got the "at comcast" part, but not sure about the "obvious". Is it only
"biff"?

Thanks,
Les

"Biff" wrote:

Hi!

If you're still checking this thread contact me at:

xl can help at comcast period net

Remove the can and change the obvious.

This can be done but I'll bet it's not going to "work" like you think.

The multiple dispatches of a particular unit can be dealt with but it's a
somewhat involved process.

Biff

"WLMPilot" wrote in message
...
I want to continue and yes, I understand the limitations of the cells and
the
problem with the same ambulance being dispatch several times. I want to
proceed and figure this out. The time indications I will adjust to 5 or
10
min increments. The basic problem is that I want compare time header and
see
if it falls in any of the ranges of the start/stop times. If so, then
fill
that cell with a color thus creating a bar graph.

"Biff" wrote:

Hi!

What you want to do is called a Gantt chart. Basically, it's fairly
simple
to do, however, you have a couple of problems!

If you want to chart the time range from 6:00 AM to 18:00 in increments
of 1
minute you will run out of cells (in a row - A1:IV1) at 10:15 AM. There
are
only 256 columns and you have 720 minutes in your time range.

Another issue is using TEXT to represent the times. It would be *MUCH*
easier to use normal time entries (any format).

Another issue, and this will be the most difficult to deal with, is, how
many times will a unit be dispatched? If a unit will only be dispatched
once
then it's really simple, no problem! If a unit might be dispatched 10
times,
ugh!

The basic logic is to compare the the TIME in row 1 to the start and stop
times of the dispatched unit.

Let me know how (if) you want to procede.

Biff

"WLMPilot" wrote in message
...
I work for an ambulance service. I wish to track, via a horizontal bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4
digits
for time without dropping leading zero), ie A1=0600, B1=0601, C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than
A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1, A3=Medic
2,
etc

Somewhere in the spreadsheet, I have a section that I enter the
dispatch
times and the end time of each call. Lets say column BA1:BA10 equals
start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times 0600 -
1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately the
timeslot for each call. So if Medic 1 was on a call from 0605 to 0700,
the
cells for Medic 1 under those times would fill in. I know this will be
a
conditional formatting, but not sure how to have those cells reference
the
start/stop array in order to know whether to fill in or not. Any cells
that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the color
fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....|
0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les

xl help at comcast dot net

Remove the spaces and change <at and <dot

Or, post your address!

Biff

"WLMPilot" wrote in message
...
I got the "at comcast" part, but not sure about the "obvious". Is it only
"biff"?

Thanks,
Les

"Biff" wrote:

Hi!

If you're still checking this thread contact me at:

xl can help at comcast period net

Remove the can and change the obvious.

This can be done but I'll bet it's not going to "work" like you think.

The multiple dispatches of a particular unit can be dealt with but it's a
somewhat involved process.

Biff

"WLMPilot" wrote in message
...
I want to continue and yes, I understand the limitations of the cells
and
the
problem with the same ambulance being dispatch several times. I want
to
proceed and figure this out. The time indications I will adjust to 5
or
10
min increments. The basic problem is that I want compare time header
and
see
if it falls in any of the ranges of the start/stop times. If so, then
fill
that cell with a color thus creating a bar graph.

"Biff" wrote:

Hi!

What you want to do is called a Gantt chart. Basically, it's fairly
simple
to do, however, you have a couple of problems!

If you want to chart the time range from 6:00 AM to 18:00 in
increments
of 1
minute you will run out of cells (in a row - A1:IV1) at 10:15 AM.
There
are
only 256 columns and you have 720 minutes in your time range.

Another issue is using TEXT to represent the times. It would be *MUCH*
easier to use normal time entries (any format).

Another issue, and this will be the most difficult to deal with, is,
how
many times will a unit be dispatched? If a unit will only be
dispatched
once
then it's really simple, no problem! If a unit might be dispatched 10
times,
ugh!

The basic logic is to compare the the TIME in row 1 to the start and
stop
times of the dispatched unit.

Let me know how (if) you want to procede.

Biff

"WLMPilot" wrote in message
...
I work for an ambulance service. I wish to track, via a horizontal
bar
graph, the call times. Here is how it would be set up:

Cells A1 through AZ1 (as example) equals times (text format to get 4
digits
for time without dropping leading zero), ie A1=0600, B1=0601,
C1=0602,
D1=0603...AZ1=1800 (military time). I know I need more cells than
A1-AZ1,
but just an example

Cells A2 - A10 siginfiy which ambulance unit, ie A2=Medic 1,
A3=Medic
2,
etc

Somewhere in the spreadsheet, I have a section that I enter the
dispatch
times and the end time of each call. Lets say column BA1:BA10
equals
start
times and BB1:BB10 equals stop times.

What I want to do is have the chart section created by the times
0600 -
1800
and ambulance number (Medic 1, Medic 2, etc) fill in appropriately
the
timeslot for each call. So if Medic 1 was on a call from 0605 to
0700,
the
cells for Medic 1 under those times would fill in. I know this will
be
a
conditional formatting, but not sure how to have those cells
reference
the
start/stop array in order to know whether to fill in or not. Any
cells
that
were not filled in would indicate when the unit was not on a call.

Below is an example of the above, but I use X's to represent the
color
fill
for a cell

|0600|0601|.....| 0607| 0608| 0609|....| 0615| 0616|....|
0700|
Medic 1 | | | |XXXX|XXXX|XXXX|XX|XXXX|XXXX|XX|XXXX|

Any help greatly appreciated,
Thanks,
Les