[Solved] Chart for arrival and departure times

[ZManMatt]

Greetings,

New to the forums, but not to OO. Been using it for general spreadsheet work for....a while. I usually go to Google for answers but I'm coming up vastly short this time.

I am working on a railroad dispatching simulation where certain trains enter a small yard, do some work, then depart. I am trying to make sure I don't have too many trains for track so I am trying to use a Stacked Bar graph to visualize what trains are at the yard when.
Column A are the Train Symbols
Column B is the Arrival Time (formatted to HH:MM) in 24hr time.
Column C is the Departure Time (formatted to HH:MM) in 24hr time.
Column D is the Time Spent (using the =(C2-B2+(C2<B2)) formula for over midnight calculations; formatted to HH:MM).

I'd like the X Axis data to be Column A data - this is about the only thing I've successfully managed.

I'd like the Y Axis to run from 04:30 to 04:30 (24 hrs) - I get errors trying to do this because the minimum and maximum are the "same." I could pare it down to 04:45 to 01:30 but the problem still remains as the maximum is "lower."

I'd like the graph to show a bar between the Arrival and Departure times - currently the bars are kinda all over the place because the Y Axis doesn't cooperate.

For instance:
Q640 arrives at 04:45 and departs 05:45, I'd like a bar between those two times on a line.
Q217 arrives at 1930 and departs at 00:30, so on and so forth.

I hope I've adequately described what I'm looking for and the issues I'm running in to.

Thank you!

Edit: Changed subject, was Chart Assistance  Make your post understandable by others  -- MrProgrammer, forum moderator

[Lupp]

Welcome to the forum,
However,

I hope I've adequately described what I'm looking for and the issues I'm running in to.

you may hope so in vain. I can hardly imagine a contributor being capable of handling your problem properly and without wasting hours with preparations. Somebody actually trying to help you will need the real thing: your .ods document.

For attaching (uploading) a file there is a guide:
See: viewtopic.php?t=8289

[ZManMatt]

Well, I kept messing around with it and was getting ready to post my .ods file when I finally stumbled upon a result that worked.

I tried a stacked bar (percentage) which got all my data on one line. I dropped the arrival bar's transperency to 100% so it disappeared, leaving just the duration bar I had been looking for. I don't understand why the stacked bar by default didn't put my data on one line instead of several bars next to each other but this worked for me.

[ZManMatt]

Here's my .ods for anyone interested.

[ZManMatt]

I got the Y axis to cooperate by adding 24 hours to the maximum time, so 04:30 the next day becomes 28:30. The program took care of the rest. However, I have to change the Major Interval back to 1:00:00 every time I make a change as it keeps trying to force 24:00:00.

[Villeroy]

I got the Y axis to cooperate by adding 24 hours to the maximum time, so 04:30 the next day becomes 28:30. The program took care of the rest. However, I have to change the Major Interval back to 1:00:00 every time I make a change as it keeps trying to force 24:00:00.

I can't reproduce this problem with LibreOffice 25.2. OpenOffice is obsolete since a very long time.

[Alex1]

It happens with LibreOffice too. Y axis scale parameters are reset to automatic.

[Zizi64]

I got the Y axis to cooperate by adding 24 hours to the maximum time, so 04:30 the next day becomes 28:30.

When you must calculate with DateTime values, then it is better to use full DateTime values for entering the input data (my example is in ISO format):

Code: Select all

2025-06-04 09:06:00
2025-06-03 05:06:00
______________________
1899-12-31 04:00:00

Then a pure substraction will give you an exact numerical result. And you can format the result as you want:

Code: Select all

[HH]:MM:SS
28:00:00

[MrProgrammer]

I tried a stacked bar (percentage) which got all my data on one line.

That is a creative idea which seems to work. I don't recall using that type of chart myself.

When you must calculate with DateTime values, then it is better to use full DateTime values for entering the input data (my example is in ISO format):

I use DateTime values unless every time is part of the same day. Your spreadsheet has rows with times in different days. Your formula for over-midnight duration will produce the wrong value if the duration is longer than 24 hours.

I'd like the graph to show a bar between the Arrival and Departure times

Although you have a solution, I have used XY Scatter charts to display this sort of information. On the Data sheet, to keep the formulas simple, the arrival and departure times begin in row 3 (2 must be empty) and are spaced every 4th row: 3, 7, 11, 15, …. X and Y data points are calculated by formulas in columns E and F. I used Leave Gap for Data Series Y → Options → Plot Missing Values (lines 6, 10, 14, 18, …).

202506041446.ods

XY Scatter chart

(19.51 KiB) Downloaded 4 times

However, I have to change the Major Interval back to 1:00:00 every time I make a change as it keeps trying to force 24:00:00.

This nuisance has been around for a long time. I don't expect anyone to fix it.

Somebody actually trying to help you will need the real thing: your .ods document.

I often ignore posts without an attachment.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Lupp]

Since the issue isn't marked solved yet, I attach a "as-if-solution" without charts based on a concept I used decades ago for a similar purpose.
Please be patient enough to read first the intro. If you start with the visualization sheet, you may immediately reject my suggestion without need.

aoo112815ConcurringTimeLines_DifferentApproach.ods

(47.4 KiB) Downloaded 10 times

[cwolan]

@Lupp
Could you please provide a version of the file that OpenOffice will interpret correctly?
(no jumbo size i.e 16384 columns).

[Alex1]

Just change the formula in DataAndVisualisation.F3 into =INT(MIN(B5:B14)).

[cwolan]

Thanks Alex1.
Although it may come as a surprise, I knew about the B:B reference in cell F3.

By interpret correctly I also meant that the Lupp's file was saved in ODF v1.4 (I should have added "etc." after the word "columns"). Who am I to judge what could be lost due to format conversion?

[Lupp]

Just change the formula in DataAndVisualisation.F3 into =INT(MIN(B5:B14)).

Sorry.
I knew the problem, but it didn't come to my consience. Of course I should also have tested with my AOO 4.1.7. Now I did, and everything worked as intended after the tiny change.

Thanks to Alex1 for the solution.
BTW: If there are no numbers in column B below row 14 you can also use "=INT(MIN(B5:B1004))" or even "=INT(MIN(B5:B1000004))"without an efficiency issue. The "B:B" now supported by LibO only avoids problems with the insertion (or probably deletion) of rows.

[MrProgrammer]

The "B:B" now supported by LibO only avoids problems with the insertion (or probably deletion) of rows.

Insertion and deletion of rows are just Move operations. Rows are moved down (to create a gap) or up (to overlay previous data). In most situations formulas like =INT(MIN(B5:B14)) are adjusted automatically by Calc to include inserted rows or to remove deleted rows. I use OpenOffice without the B:B syntax and have no difficulty with formulas when inserting or deleting rows. I'll admit that the new syntax is convenient, especialy for many people new to Calc who don't understand concepts like relative references or how Copy and Move are implemented.

The details on how insertion and deletion operate are in this tutorial. They may seem complex, however I find that the complexity is just due to the desire to have Calc operate the way that people expect. I don't really think about all these details when I'm inserting or deleting, since I know that Calc will behave in a reasonable manner. I won't further discuss move, insert, or delete operations in this topic since it concerns creating a chart.
[Tutorial] Formula Adjustments during Copy and Move