time plots/charts in Excel

[Leoborealis]

I am trying to plot usage time for a device we use; data is in the following
format:

A B C
Online 23:27:39 12-Oct-08
Online 23:30:54 12-Oct-08
Online 23:34:09 12-Oct-08
Online 23:37:24 12-Oct-08
Offline 23:40:37 12-Oct-08
Offline 23:43:50 12-Oct-08
Offline 23:47:03 12-Oct-08
Offline 12:00:28 13-Oct-08
Offline 12:03:41 13-Oct-08
Offline 12:06:54 13-Oct-08
Online 12:10:10 13-Oct-08
Online 12:13:26 13-Oct-08
Online 12:16:42 13-Oct-08
Online 12:19:58 13-Oct-08
Offline 12:23:11 13-Oct-08
Offline 12:26:24 13-Oct-08
Offline 12:29:37 13-Oct-08
Offline 14:51:13 13-Oct-08
Offline 14:54:26 13-Oct-08

I'm trying with no success to plot online time by day and by time windows,
on a 24 hour scale.
Help and tips will be much appreciated.
Thank you.

 

[PBezucha]

Leo,
Obviously the time intervals are different in the record. Therefore you have
to employ scatter (xy) chart. To prepare data for it:
(1) Be sure that values 18-Oct-80 etc. are formatted as dates, and 20:23:12
as times.
(2) Place sums in the forth column: “= B1 + C1â€, and downwards. General
time data appear as results.
(3) Format these data range properly, creating your user format. It will be
adopted in the chart for x-axis text, and therefore must be short and
fitting. You will have to omit year and probably month, and on the other side
minutes and seconds.
(4) Link 1’s for online mode, 0’s for offline: in the fifth column
“=if(A1=â€Onlineâ€,1,0)â€

(5) Select correct range in columns D and E, and by means of chart wizard
choose scatter chart.
(6) Set proper time scale: do not be shocked by offered values round 36900
(these are the whole days count from the beginning of 1900, including current
day fraction), and input the whole expressions, e.i. 12-Oct-08 23:00:00 for
the beginning, and 13-Oct-08 15:00:00 for the end on x-axis. You can select
also interval on the axis e.i. 3:00:00.
(7) Select sufficiently thick line with no markers; tick off “smooth lineâ€.

Petr

 

[Leoborealis]

Petr,
Thanks for the guidance so far.
I followed your advice and added columns D and E; so far so good.

A B C D E
Online 23:34:09 12-Oct-08 12-10-08 23:34 1
Online 23:37:24 12-Oct-08 12-10-08 23:37 1
Offline 23:40:37 12-Oct-08 12-10-08 23:40 0
Offline 23:43:50 12-Oct-08 12-10-08 23:43 0
Offline 23:47:03 12-Oct-08 12-10-08 23:47 0
Offline 12:00:28 13-Oct-08 13-10-08 12:00 0
Offline 12:03:41 13-Oct-08 13-10-08 12:03 0
Offline 12:06:54 13-Oct-08 13-10-08 12:06 0
Online 12:10:10 13-Oct-08 13-10-08 12:10 1
Online 12:13:26 13-Oct-08 13-10-08 12:13 1
Online 12:16:42 13-Oct-08 13-10-08 12:16 1
Online 12:19:58 13-Oct-08 13-10-08 12:19 1
Offline 12:23:11 13-Oct-08 13-10-08 12:23 0
Offline 12:26:24 13-Oct-08 13-10-08 12:26 0
Offline 12:29:37 13-Oct-08 13-10-08 12:29 0
Offline 14:51:13 13-Oct-08 13-10-08 14:51 0
Offline 14:54:26 13-Oct-08 13-10-08 14:54 0
Offline 14:57:39 13-Oct-08 13-10-08 14:57 0
Online 15:00:55 13-Oct-08 13-10-08 15:00 1
Online 15:04:11 13-Oct-08 13-10-08 15:04 1
Online 15:07:27 13-Oct-08 13-10-08 15:07 1
Online 15:10:43 13-Oct-08 13-10-08 15:10 1
Online 15:13:59 13-Oct-08 13-10-08 15:13 1
Online 15:17:16 13-Oct-08 13-10-08 15:17 1
Online 15:20:32 13-Oct-08 13-10-08 15:20 1
Offline 15:23:45 13-Oct-08 13-10-08 15:23 0
Offline 15:26:58 13-Oct-08 13-10-08 15:26 0
Offline 15:30:11 13-Oct-08 13-10-08 15:30 0
Offline 15:33:24 13-Oct-08 13-10-08 15:33 0
Offline 15:36:37 13-Oct-08 13-10-08 15:36 0
Offline 15:39:50 13-Oct-08 13-10-08 15:39 0

Now, data ranges: I have selected D and E but it won't work.
I have to plot I believe C and D for E=1, how do I do that?
And how do I enter time on Y, it turns a value if entered in 12:12:12 format.
I'm completely lost, please lend me a hand.
Thanks.

 

[PBezucha]

Leo,
Correctly, you have the summed time values in the column D :
13-10-08 15:36, …,
and 0’s and 1’s in E.
I suppose you know the way how to use chart wizard for xy-scatter chart. You
can prove the ability on your own sample (0, 1, 2, … in column A, 0 ,1 3, …
in column B…). Define line and no markers.
Then you certainly see that x-values are the times in D and y-values 0’s and
1’s in E. So, have you selected right range? Why do you write you should
enter time in Y????
If still nothing or something awful happens with your data, then the clue
may be in formatting the time. You must be sure you have set suitable format
as I indicated. It is not quite easy with Excel, but you must try to adapt
proper feature in setting user format to your time range. Use help.
At worse, send me your work on petr.bezucha at vuanch.cz.
Petr