The 'time' data from a linked file is sometimes seen as text, sometimes as time

[anonymo]

I have linked a text file to a Calc (Version 7.0.3.1, under Ubuntu) file. (I also tried this in Windows, with the same results.) The text file is being updated via a serial connection and the link is being updated. The data includes a timestamp, and a reading of atmospheric pressure. Everything works beautifully: I have a growing set of data in two columns that I want use in a chart.

BUT

When I chart it, the time data (eg 08:00:00, hh:mm:ss) from the linked file is seen as text, while the numerical data (e.g. 1008.1) is seen as numbers. So an XY chart takes the times as labels. If I edit the X axis, and say that it should not be formatted according to source format, but rather as time (hh:mm:ss), the data points are in a sequence, plotted against an X axis that has three markers, 00:00:00, 00:00:00 and 00:00:00. It is not seeing the data as time.

BUT

If I use a formula in another column like ‘=B3+24’ or ‘=B3*1’, and use that column as the x axis data, it works, it is seen as time data. However, the formula ‘=B3’ doesn’t work, it is treated as text.

In other words Calc sometimes sees the time data as a time (and can successfully manipulate it), and sometimes sees the time data as text, and cannot manipulate or use it.

Puzzling.

[anonymo]

Copying and pasting the data gives the same problem. When I then format that time column as hh:mm:ss the time stamp gets an inverted comma added to it: ’ '09:03:00 '. When I remove that initial inverted comma, then I finally get Calc to recognise that the data is time in the format hh:mm:ss.

[ajlittoz]

The initial apostrophe ' is a formatting directive to force text value. You should look at your data source to see exactly what characters are in the time field. The fact that it is left-aligned in the picture above clearly shows it is interpreted as text. What is your locale? There does not seem to be time acceptance patterns as for dates. Consequently, input data must exactly match hh:mm:ss. Any other character will cause text interpretation.

[anon73440385]

Copying and pasting the data gives the same problem. When I then format that time column as hh:mm:ss

Don’t try to fix input format problems with after-input-formatting. Formatting does not change data types. Example: If your time is being imported as text you never can fix that import-failure by formatting. You need to either avoid the import-failure or convert the imported data (Hint: Data ->Text to Column) but formatting won’t help.

[anonymo]

Using the formula ‘=B3*1’ causes the column to right align, so yes, I am aware that that is the problem. I have looked at the source data, it is a simple text file, and I can open it with GEDIT. There are no issues there, it is in the format hh:mm:ss.

[anonymo]

I tried another approach, which was to have another column convert the time string (looking at it as a string of text) into the number of hours since midnight. It was looking good, i was excited! The extra column (with the formula: " =LEFT(RIGHT(B3,5),2)+(LEFT(B3,2)*60) ") worked, and I could convert it back to time easily enough. It was working.
But, when data was appended to the bottom of the column a and b, the formula in column c was altered, so that it no longer referred to the cell on the same line as it, but to the cell one cell below that, which was empty. I had put the formula =LEFT(RIGHT(B3,5),2)+(LEFT(B3,2)*60) in cell D3, opposite the first data in cells A3 and B3. Below that was the formula with B4, and so on. When data was appended to row 4 the formula was altered to refer to cell B5.

[ajlittoz]

Your problem may be in the initial CSV file and the import mechanism. What happens if, instead of creating two fields, one for the date and one for the time, you merge them in a single one, using a space between date and time instead of the field separator?

Display in column one is a matter of choosing an adequate format for both time and date. Same for axis labels (column A directly).

[anonymo]

I think that ajlittoz has the answer. I can separate out the timestamp into date and time, in which case telling the import mechanism that the ‘time’ data is ‘date’ helps. Or I can import the date and time as one field, as you say, and that works too.
Thank you.