sorting string and numeric data in calc

[hengis]

I am collecting data every minute with a time stamp and temperature. I have had to trim the time stamp using the MId
function. I then select the temperatures which have changes to determine peaks and troughs. This results in two comumns with some times separated by mane cells containing 0. I have tried sorting the column but the sort does not sort the time stamp string.

below is a sample

0 0

0 0

0 10:39

0 0

0 0

0 0

0 0

0 0

0 0

0 0

0 0

11:00 0

0 0

0 0

0 0

0 10:50

10:54 0

10:58 0

11:06 0

0 10:31

0 10:36

0 10:37

0 0

10:49 0

11:11 0

0 11:13

0 10:48

10:51 0

Edit keme: added line breaks to reflect newlines in original post.

[PKG]

Please attach a correct sample file.

[erAck]

And please do not use the Answer field, edit your original question to provide further details and attach a file. Thanks.

[Lupp]

@keme: Now there are some of the time values in the first column, but others in the second. Nothing there resembling a temperature value.
@hengis: Time stamps should be time stamps. IMO this includes that they need to contain a date part. TOD only is (roughly) cyclic, and cannot be sorted therefore reliably.
Data logging must obey known specifications, otherwise no reliable evaluation will be possible.
Anyway the time stamps created by data logging should mandatorily be increasing strictly monotoically…

[anon73440385]

have had to trim the time stamp using the MId function

Function MID() returns text strings, hence your timestamps are no longer times (in the sense of calc) and do not numerically sort.

[keme]

@Lupp wrote:

Now there are some of the time values in the first column, but others in the second. Nothing there resembling a temperature value.

Yes. That is how they were entered.

With a single return between the value pairs, the askbot software reflows content to render the whole set as a single paragraph.

In the posting source, the pairs were on separate lines. I did not make any material change, just terminated each pair with a html break tag, so the values are now rendered more or less as entered, instead of reflowing it. I assumed that this is what @hengis intended.

[Lupp]

I assumed that this is what @hengis intended.

Yes. My comment was obsolete in a sense. However, my incapability of believing in such an intention was my reason to not edit the post “for better readability” as I sometimes do in clearer cases.

[LeroyG]

@hengis, By what is seen in the sample, it seems that times could be in whichever column, hence the problem to sort.

Additionally, a better approach could be not to MID the date, but to change the date format to HH:MM.

---

Add Answer is reserved for solutions. Please, click edit below your question to add more information. Thanks.

[erAck]

To convert a time text string to its numeric value use TIMEVALUE() and to display it as time again on the result apply a time format. For example, for

=TIMEVALUE(MID("0 12:34";3;5))

the result is 0.523611111111111 and formatted as HH:MM that is 12:34

[Lupp]

Hmmm
Time values formatted with the needed leading zeroes and regarding 24- hours a day ( HH:MM ) are also sortable as texts.
But still sorting TimeOfDay will rarely be reasonable if not is assured that they are taken the same day.

[hengis]

I see that the tine stamp is a string while the temperature data is a number. How do I convert the time (hh:mm) to a number?

[anon73440385]

See my comment above:

have had to trim the time stamp using the MId function

Function MID() returns text strings, hence your timestamps are no longer times (in the sense of calc) and do not numerically sort → Don’t use MID()

Please do **not** use *Add Answer* if you actually don't answer a question but commenting an answer or responding to another user’s comment. Please use ***add a comment*** for that purpose. Thanks in advance …

[LeroyG]

To convert the text string 10:39 to time, select the times range, choose menu Format - Cells… - Numbers tab, for Category choose Time and for Format choose 13:37, OK.

Then (while selected the same range) choose menu Data - Text to Columns… - OK.

Tested with LibreOffice 6.4.7.2 (x64); OS: Windows 10.0.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark ( → ) to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

[erAck]

Applying a number format does not change the content type from text to numeric or date/time.

[hengis]

THANKS to all. Sorry for not following protocol. I am beginning to understand Calc’s distinction between strings and numbers. I believe text needs " delimiters but if some functions like MID & Left etc are carried out on a cell’s contents the result is a string. I have tried the suggestion to convert time strings to numbers and it seems to work. I will analyse other days data to check… I found by searching the web that multiplying my selected time strings and multiplying them with 24 it resulted in a sortable number. After doing some calculations I could convert the answer to time by dividing by 24. It seemed to work but was not very elegant.

A big THANKS again for all the help.

[LeroyG]

@erAck,

Applying a number format does not change…

That is the reason of the second paragraph in the answer.