Charting start and end time 1

[Jontmke]

I have a large amount of data (7000 entries) showing data like this:

Client Start Time End Time
bkfexch1 1/6/2005 13:00 1/6/2005 15:05
bkfexch1 1/6/2005 15:00 1/6/2005 15:21

The data is from the entire month of January. I want to set up a Gantt type of chart showing the time the job ran. I have made more columns showing Day start, hour start, day end, hour end, length of job (i.e. 2:05). I just don't know enough to get this in a chart so I can see it.




Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

Hi,

This is pretty simple. Add a calclated column for DURATION (end minus start)

Then plot Client, Start Time , Duration.

Choose Stacked Bar Chart.

Format the Start Time series with NO LINE & NO FILL.

Fomat the Category Axis as Date or Time.

You might also need to modify the category axis Scale MINIMUM Value to something close to the minimum Start Time (just the date portion)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Jontmke]

It took me awhile but I got it thanks.

Any idea how I can devide the data up, say by days?
7000 entries over 30 days is too cumbersome.

Thanks

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

How do you want to look at your data?

What is the date/time axis span?

Is that all 7000 data points?

You could use a dynamic named range for each of the series including the category series using the OFFSET function in Insert/Name/Define.

Check this FAQ How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Jontmke]

This data is a list of all the backup jobs for the month of January for our entire network. What I am trying to do is get a way to view this data so I can see where bottlenecks are, or as in the case I have currently, where to add a new very large backup to the system.

I want to be able to look and see how many jobs are running at a specific time. Not just when they start but when they are active. Some run for more than 30 hours.

The reporting system for this product is very limiting. I want to be able to look at something and say for instance, I can add this backup to Sunday afternoons because there is light activity. Or, gee I better move some of these off of Tuesday nights because it is very crowded.

I appreciate the help, I will take a look at the FAQ when I can get some time.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

You might want to summarize your data in variable time buckets (ie 1 hr, 2 hr, 3 hr, 15 min, 30 min etc) keeping in mind that the UNITS of the time value is DAYS and variable duration buckets (< 1 min, 1 to < 5 min, etc)

I'd probably analyze this data in a number of different ways.

Then lot your various summaries.

If you use DYNAMIC NAMED RANGES (How can I rename a table as it changes size faq68-1331) using the OFFSET function in Insert/Name/Define, you can control your chart's display using a Spinner and/or DropDown or some other control.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Jontmke]

I have looked at the FAQ on naming a data range. I have used your formula and named the ranges.

Thanks for the help.

I really need to take a class on this.

The statement "You might want to summarize your data in variable time buckets..." is a good idea but I just don't have the knowledge to do that. I will keep plugging along and do the best I can.

Thanks again.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

Jon,

You don't need a class! You need to teach yourself!

I've not had a class in Excel or VBA (and I'd wager that virtually ALL the top MVP's in this forum never had a class), yet I READ and EXPERIMENT with what I have read. Most of what I read is examples here at Tek-Tips, excel/vba Help files, Help on the Microsoft.com site. Books by John Walkenbach are good.

Take a concept and play around with it. Post questions here.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Jontmke]

OK, Skip.
I have been playing with the data and getting some OK charts. What I need now is to be able to pick a time frame and see how many jobs are running during that time.
Here are the fields I have created:

Client	        Start Time      End Time        Backup Time
mkecommvault2   1/1/2005 1:30   1/1/2005 1:34	00:04
mkecnvdev01     1/1/2005 1:30	1/1/2005 1:41   00:10

I also have

Day Started	Day Ended	Hour Started	Hour Finished

I have graphs that show the total time a job was run, how many were run on a specific day, how many jobs were started at specific hours of the day.
Now, I need to pick a time, say noon on sunday the 6th and find the total "active" jobs.

Any ideas?


Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

Filter your source data to show only starts on a specific day/time --

criteria1: >= that date/time
AND
criteria2: <the next day @ 12 AM

and then count the jobs

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}