Charting timed events throughout a month

  • Thread starter Thread starter trwagner1
  • Start date Start date
T

trwagner1

I thought I had a simple task to perform for work. Then, I trie
charting it in Excel and have not had any luck.

I need to take some simple data and for each day of the month, I nee
to chart some usage times (24 hour clock). What we want to identify i
where simultaneous instances occured.

Example data:

Date Start Time Stop Time
11/1/03 09:03 09:53
11/1/03 10:21 10:25
11/1/03 10:23 10:54
11/1/03 14:56 15:31
11/1/03 15:17 15:49

I've no clue how to set this up. I thought I might, but I've no
gotten anywhere with this.

I first imagined a horizontal bar chart showing dates on the Y-axis an
then the times (24 hour clock) on the bottom. But, the problem I ha
with charting this in excel is it doesn't see the relationship of th
BLOCK of time. In the example data above, I have 5 blocks of time an
2 "share" portions of the same block of time. I need to be able t
show this relationship in a chart for each month of a year (each mont
having it's own chart).

Anyone have an idea where I could begin?

Thanks

Te
 
It may be difficult to read with a month's worth of data, but you can do
the following (using your sample data in cells A1:C6) --

- Add a column to the table, with the heading 'Start'
- In row 2 of the new column, enter the formula: =A2+B2
- Add another column, with the heading 'Duration'
- In row 2 of the new column, enter the formula: =C2-B2
- Copy the formulas down to row 6
- Select the headings and data in the new columns
- Click the Chart Wizard button, and create a stacked bar chart
- In the chart, select the 'Start' series, and format it with
Border and Area set to None
 
As Debra pointed out, using a chart will probably lead to a messy
display. Here's an alternative that simulates a chart in worksheet
cells using judicious formatting.

Suppose your data are in A:C starting with row 2 as:

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
As Debra pointed out, using a chart will probably lead to a messy
display. Here's an alternative that uses color through judicious
formatting of worksheet cells to create a visually revealing display.

Suppose your data are in A:C starting with the headers in row 2 as:
Date Start Stop
11/1/2003 09:03 09:53
11/1/2003 10:21 10:25
11/1/2003 10:23 10:54
11/1/2003 14:56 15:31
11/1/2003 15:17 15:49
11/2/2003 15:20 15:21
11/2/2003 10:21 10:25
11/2/2003 10:23 10:54
11/2/2003 14:56 15:31
11/2/2003 15:17 15:49

To simplify downstream formulas and to build flexibility into the
system so that it automatically adjusts to additional data, define the
named formulas (Insert | Name > Define...)

Dates =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)
EndTimes =OFFSET(Dates,,2)
StartTimes =OFFSET(Dates,,1)


Now in some cell, say, F2, enter the first date 11/1/2003. Format it
to show just the date (dd)

Create a list of all the dates in the month in row 2

In column E, put the time slot information. In E1, enter the interval
of interest. I used 15 minutes, but that is flexible.

In E3 enter 00:00. In E4, enter =E3+TIME(0,$E$1,0). Copy E4 down
column E until you get to the next midnight less the-selected-time-
interval. So, in my case, I stopped copying when I had 23:45 in a
cell.

In F3 enter the formula
=SUMPRODUCT((Dates=F$2)*(StartTimes<=$E4)*(EndTimes>=$E3))

Format F3 to have a pattern of light green and a light gray font
(Format | Cells... | Patterns tab and the Font tab)

Set three conditional formatting criteria (Format | Conditional
Formatting...):

Condition 1:

The value (first drop down box) equals (2nd drop down box) 1
(refedit/text field) => Pattern is light blue and the font is light
gray (click the Format... button, then the Patterns tab and then the
Font tab).

Condition 2: (click the Add>> button to see additional conditions)
The value equals 2 => Pattern is light yellow and the font is light
gray

Condition 3:
The value is greater than or equal to 3 => Pattern is tan and the font
is light gray

Click enough OKs to exit the conditional formatting dialog box.

Copy F3 all the way across and all the way down to cover the dates in
row 2 and the times in column E.

Change the column width of the columns containing the display to 'Best
Fit' The result should be a rather narrow width of under 3 units.

Change the zoom setting to 50% (or whatever is appealing from both an
aesthetic and content perspective).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Excel chokes when subtracting a larger time from a smaller time. In
case there are end times after midnight, you should probably add a
column for Stop, which has the formula =A2+C2. Your duration will be
this new column minus Deb's new column. Now make the chart with the
data in Start and Duration.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Debra said:
It may be difficult to read with a month's worth of data, but you can do
the following (using your sample data in cells A1:C6) --

- Add a column to the table, with the heading 'Start'
- In row 2 of the new column, enter the formula: =A2+B2
- Add another column, with the heading 'Duration'
- In row 2 of the new column, enter the formula: =C2-B2
- Copy the formulas down to row 6
- Select the headings and data in the new columns
- Click the Chart Wizard button, and create a stacked bar chart
- In the chart, select the 'Start' series, and format it with
Border and Area set to None
Click to expand...
 
Back
Top