Gantt Chart (possibly) 1

[blindlemonray]

Hi,

I started a thread a little while ago but did not get a solution (thread68-1246464).

I want to create a chart that shows when a caller in on the phone. The information I have is caller, time call made & length of call.

What I want to show is something like a barcode effect, the axis by caller & Time of day when the call started and how long the caller was on the phone for which.

I thought I would be able to do this through a gantt chart by I have tried for months and now my brain is melting. I tried looking here

http://peltiertech.com/Excel/Charts/GanttLinks.html

but this is not relevant.

This is a sample of the data I have. You can see user 545 on the phone at 9.07 for 1 min and then again at 9.09 for 37 secs. This is what I need to show in a chart.

09:07:00 00:01:07 545
09:09:00 00:00:37 545
09:11:00 00:00:23 546
09:13:00 00:00:35 545
09:14:00 00:00:28 546
09:17:00 00:01:47 545
09:20:00 00:01:30 546
09:25:00 00:01:36 545
09:26:00 00:01:14 546
09:33:00 00:00:17 546
09:39:00 00:00:23 545
09:39:00 00:00:11 546
09:40:00 00:00:06 546
09:46:00 00:01:42 545
09:48:00 00:02:09 546

Any ideas much greatly appreciated

 

[SkipVought]

Hi,

This is going to be difficult to do, because of the indeterminant number of calls and the different number of calls for each ID. Here's why. The chart data for each ID has this form...
[tt]
Start time
Duration1
Lapse1
Duration2
Lapse2
...
Durationn
Lapsen
[/tt]
Sort your table in ID sequence.
Calculate Lapse as ...
[tt]
D2: =IF(C2=C3,(A3-A2)+B2,0)
[/tt]
The only way that I would know to generate this is using VBA code. Post in Forum707 for this kind of help.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[blindlemonray]

Hi Skip,

Thanks for that. Lets say I just concentrate on one the caller for now. I am sure I can probably write some code or a macro to get the required data into the correct format as you suggested.
===========
Start time
Duration1
Lapse1
Duration2
Lapse2
...
Durationn
Lapsen
============

What I can't figure out is how to get it to display in the format I need (the Gantt chart type thing resembling a bar code). I have tried looking at bar charts in Excel but I can't figure out how to actually get the data to display in one bar which is what I guess I have to do. Formating the chart so it looks as though it stops and starts.

 

[SkipVought]

here's what your sample data looks like...
[tt]
545 546
Strt 9:07:00 9:11:00
Dur1 0:01:07 0:00:23
Lps1 0:03:07 0:03:23
Dur2 0:00:37 0:00:28
Lps2 0:04:37 0:06:28
Dur3 0:00:35 0:01:30
Lps3 0:04:35 0:07:30
Dur4 0:01:47 0:01:14
Lps4 0:09:47 0:08:14
Dur5 0:01:36 0:00:17
Lps5 0:15:36 0:06:17
Dur6 0:00:23 0:00:11
Lps6 0:07:23 0:01:11
Dur7 0:01:42 0:00:06
Lps7 0:00:00 0:08:06
Dur8 0:02:09
Lps8 0:00:00
[/tt]
1. Name your series ranges: Select ALL the data/Insert > Name > Create > Create names in LEFT COLUMN This will save you time in the long run when you ADD additional columns of data. Simply redo this step.

2. Create the chart (ONE TIME ONLY). Use the STACKED BAR. In Source Data, Data Range TAB, select Series in ROWS.. In the Series TAB, Select each series, IN SEQUENCE, one at a time, and replace the range reference after the BANG ! with the Range Name in the corresponding row, for instance
[tt]
was
=ChartData![red]$B$2:$C$2[/red]
change to
=ChartData!Strt
[/tt]
If, when you add a new column, you have NEW RANGE NAMES, you will have to add those series to your chart AND change the series references as well (ONE TIME). To add new series to your chart, COPY the range of the new series, select the chart, Edit > Paste special.... Then modify the range references as above.

FORMATTING the chart:
1. select the X-axis (Your ID names) - Format axis - Scale TAB: Categories in REVERSE order

2. select the y-axis (Your TIME values) - Format axis - Minimum: enter the minimum time, like 9:05:00, Major Unit: enter an interval for the major gridlines like 0:05:00

3. Cycle thru the series: use your UP ARROW key to select each series, starting with series 1. Format > Selected Data Series Every ODD series: No Border, No Area, Every EVEN series the Border & Area of your choice.

This can be tedious and prone to error. A macro or two could help.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[blindlemonray]

Hi Skip,

I got some serious macros to write but that is exactly what I have been trying to figure this out for quite some time now. Thats absolutley fab.....

Many many many thanks
BLR

 

[SkipVought]

Post in forum707 or VBA (macro) help.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}