Time - Distance plot...

[Mark]

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?

 

[Bernard Liengme]

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]

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.

 

[Fred]

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.