Converting time to a text string with additions in Calc

English
#1

I’m strying to set up a facsimile of an old railway timetable. I have columns for “duration”, “wait” and “arrival time” and “departure time”, and locations in rows. Duration and wait are manually entered for each location, the arrival time is calculated from the previous location’s departure time and the duration, the departure time is calculated from the arrival time plus the wait time.
That all goes well, and can work perfectly.
In the “Working Timetable” - in British Rail days at least - sometimes there are also shown symbols between the hrs and minutes to give extra information ie. 10X24 means pass this location at 10:24 and change lines; 10S25 means stop at 10:25 and pick up staff; 10*26 means arrive at 10:26 and wait for another train to pass; etc…

I can set up a 3rd column to manually enter these extra symbols for each location, but how can I get a time to display in this sort of format with the extra characters between the hrs and minutes?

#3

I think it would be more convenient to answer in a sample file that you prepare and upload to the forum. Try to give as many different situations as possible.

#4

Thanks. Attached is a sample, in the final printout only columns A, I, J & K would be displayed, and I have entered the values in column J manually, to show how I want the output to appear. But I also want to be able to adjust the duration and wait times to see how changes will affect all times further down the timetable.
I can suppress the display of arrival times when it is a pass at that location, and also change the format so the seperator charcter is a space rather than a : - but how to add in different seperator characters?
Thanks.
timetabe sample.ods (21.9 KB)

#5

Let’s try for J3:

=TEXT(H3;"HH")&IF(OR(E3="R";E3="");" ";E3)&TEXT(MINUTE(H3);"00")

If it’s OK, drag the formula down.

2 Likes
#6

No , I just get a repeat…
image

#7

I’m using this version, if this helps
image

#8

I do not see differences between the results of the formulas (column L) and the column J you filled in (see attached file).
timetabe sample.ods (12.2 KB)

#9

That is strange.
In your version of the file which I d’loaded, it is exactly as expected. When I copy and paste the formula back into my file, it just gives a copy of the formula as text. I’ve even tried reformatting the cells to be “General” but no success.
Any more ideas?

#10

Yes, the cells in column J are in text format.
Select J3:J25 in your file and change the format to General (Standard).
(The hotkey for the Standard format is Ctrl+Shift+6.)
Then enter the formulas.

#11

Got it!

There appears to be an extra ’ in the formula entry field. No idea how that got there!
image

When that is removed, it’s all as expected.

Many thanks!

1 Like
#12

If we change the text format to another format and Calc can interpret the text value of the cell as a number or date, an apostrophe is placed before the value.
I did not see this phenomenon in your file, because I have different locale settings.
Good luck!

1 Like
#13 
=REGEX(TEXT(H3;"HH:MM");":";IF((E3="R")+(E3="");" ";E3))
=SUBSTITUTE(TEXT(H3;"HH:MM");":";IF((E3="R")+(E3="");" ";E3))
2 Likes
#14

Yes, both of those work too!
Many thanks

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.