How to create a time chart ?

  • Thread starter Thread starter Ayato
  • Start date Start date
A

Ayato

Hi,

I have a table that contains some times (log-in log-out information)
I want to create a time chart out it any suggestion ?

For example:

John, 08:00 (log in)
John, 12:00 (log out)
John, 13:00 (log in)
John, 18:00 (log out)

I want ot put that in a graph like this:

08 09 10 11 12 13 14 15 16 17 18 19
John xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx


@+
Ayat
 
Ayato -

This is a floating bar chart, much like a gantt chart. Transpose the
data as shown; if you have lots, a pivot table might help. The first
four time columns are the input times, the second four columns contain
formulas.

In-1 Out-1 In-2 Out-2 Start Morning Lunch Afternoon
John 08:00 12:00 13:00 18:00 08:00 04:00 01:00 05:00
Mike 08:15 12:00 12:30 18:45 08:15 03:45 00:30 06:15

Assuming this is in A1:I3, the formula in F2 is =B2, which is filled
down as far as needed. The formula in G2 is =C2-B2 which first is filled
right, then down.

Select A1:A3 (yes, including the blank cell above "John", and you'd best
keep it blank, so Excel knows what you're plotting), then hold Ctrl
while selecting F1:I3, so there are two regions selected. Start the
chart wizard, and make a stacked bar chart, with series in columns.
Format the Start and Lunch series to be invisible (no border, no fill),
and the Morning and Afternoon bars will float in the chart.

- Jon
 
Back
Top