Prev: emailing worksheet
Next: Stop XL from changing format?
From: Mark on 2 May 2010 06:00
I am trying to create a time - distance plot of a series of trains in a
timetable. Each train will be represented as a continuous diagonal line from
start to finish. The stations are represented by a number giving their
mileage away from the start position. The times are represented in the hhmm
format of the 24hr clock, e.g. 1830 for half past six in the evening.

Couple of problems here...

Firstly, Excel doesn't like the time format with no colon, even if I'm using
a custom format of hhmm. At the moment the times are displayed with a colon
which means the times take a lot longer to enter. Any ideas to force Excel
to accept hhmm?

Secondly, where a train does not stop at a particular station but runs
through non stop, there is no time coordinate for it on the timetable and
consequently no data point on the graph, hence this results in a gap in the
diagonal line until the next time the train stops. Any ideas for making this
line continuous without entering a time, e.g. a line drawn between the two
times the train stops over two or more data points, instead of
line-gap-line?

Any ideas?


From: Bernard Liengme on 2 May 2010 09:04
Point 2: train does not stop
Rather than leave the cell blank, enter =NA() which will display as #N/A and
be ignored by the chart engine

Point 1
Suppose you enter time as, for example 1230, in A1.
In another cell use =TIME(INT(A1/100),MOD(A1,100),0) to convert to time. Use
the the column of the first cells to enter data and the second column for
you chart
best wishes

"Mark" <m.underwood(a)nospam.com> wrote in message
news:IPGdnaECCsvH0EDWnZ2dnUVZ8kKdnZ2d(a)giganews.com...
> I am trying to create a time - distance plot of a series of trains in a
> timetable. Each train will be represented as a continuous diagonal line
> from start to finish. The stations are represented by a number giving
> their mileage away from the start position. The times are represented in
> the hhmm format of the 24hr clock, e.g. 1830 for half past six in the
> evening.
>
> Couple of problems here...
>
> Firstly, Excel doesn't like the time format with no colon, even if I'm
> using a custom format of hhmm. At the moment the times are displayed with
> a colon which means the times take a lot longer to enter. Any ideas to
> force Excel to accept hhmm?
>
> Secondly, where a train does not stop at a particular station but runs
> through non stop, there is no time coordinate for it on the timetable and
> consequently no data point on the graph, hence this results in a gap in
> the diagonal line until the next time the train stops. Any ideas for
> making this line continuous without entering a time, e.g. a line drawn
> between the two times the train stops over two or more data points,
> instead of line-gap-line?
>
> Any ideas?
>
From: Mark on 2 May 2010 10:32

"Bernard Liengme" <bliengme(a)TRUENORTH.stfx.ca> wrote in message
news:%23PvCIgf6KHA.3880(a)TK2MSFTNGP04.phx.gbl...
> Point 2: train does not stop
> Rather than leave the cell blank, enter =NA() which will display as #N/A
> and be ignored by the chart engine
>
> Point 1
> Suppose you enter time as, for example 1230, in A1.
> In another cell use =TIME(INT(A1/100),MOD(A1,100),0) to convert to time.
> Use the the column of the first cells to enter data and the second column
> for you chart
> best wishes

Does (say) 930 get handled OK by =TIME(INT(A1/100),MOD(A1,100),0) and
converted to 0930, since Excel omits the original 0 when formatted as
number?

If I use =NA(), what is the result of running the
=TIME(INT(A1/100),MOD(A1,100),0) formula against it, as I intend to just
replicate the formula down the timetable column.

Is it possible to hide the =NA() in the resulting timetable as I want to be
able to print both?

Thanks for these, gives me some idea as to what to work with.


From: Fred on 2 May 2010 13:30
"Mark" <m.underwood(a)nospam.com> wrote in message
news:IPGdnaECCsvH0EDWnZ2dnUVZ8kKdnZ2d(a)giganews.com...
> Excel doesn't like the time format with no colon

One way to simplify the entry of times (Excel 2000) is to use the
AutoCorrect feature
i.e. Tools > AutoCorrect > and Add the new command Replace ++ With :
(colon).

Thus 19++30 is converted to 19:30.

--
Fred


 | 
Pages: 1
Prev: emailing worksheet
Next: Stop XL from changing format?