Libre Office Calc

English Applications
#1

I’ve recently run into a couple of problems with trying to make charts in Calc. It is trivial to do this in Google Sheets, but I want to do this with local files.

I export glucose readings in a csv file to either Calc or Google Sheets when I get nowhere with Calc. One column is the date and time of day. The csv file comes through in descending order and I want the dates in ascending order to see progress over time. In Calc, the data sorts with the month and then the first number of the date. So the 21st day comes before the 5th day. In other words, out of order. I have not checked to see what order the time is sorted in. One approach that I thought would work is to just reverse the entries on the sheet for charting, but I can find no way to do that either.

Similarly, I added a column with one of three possible entries: morning, noon or night. I wanted to see three bars for each date. Instead, I get 3 labels at the bottom of the bar but only one data point. Calc may not do sophisticated charting like Excel, but it should be able to do this. Are there add-ons that will accomplish these things?

#2

Hi
If you format the date/time column(s) as a date format it should sort that out?

Can you post some example data of the cvs file say 6 records (two days)?

#3

In Excel, I format as time, not date, and it works. In Calc, formatting as time does not work

    |Date and Time|
|---|
|10/24/16, 8:48 AM|
|10/20/16, 11:16 PM|
|10/20/16, 6:09 PM|
|10/20/16, 8:20 AM|
|10/16/16, 11:22 PM|
|10/16/16, 6:48 PM|
|10/16/16, 8:34 AM|
|10/13/16, 12:03 AM|
|10/12/16, 5:19 PM|
|10/12/16, 9:10 AM|
|10/9/16, 11:18 PM|
|10/9/16, 4:58 PM|
|10/9/16, 8:16 AM|
|10/5/16, 11:36 PM|
|10/5/16, 5:18 PM|
|10/5/16, 7:29 AM|
|9/29/16, 11:33 PM|
|9/29/16, 4:28 PM|
|9/29/16, 8:41 AM|

That is the unsorted data. Here is the data I ask to sort in ascending order

    |10/12/16, 5:19 PM|
|---|
|10/12/16, 9:10 AM|
|10/13/16, 12:03 AM|
|10/16/16, 11:22 PM|
|10/16/16, 6:48 PM|
|10/16/16, 8:34 AM|
|10/20/16, 11:16 PM|
|10/20/16, 6:09 PM|
|10/20/16, 8:20 AM|
|10/24/16, 8:48 AM|
|10/5/16, 11:36 PM|
|10/5/16, 5:18 PM|
|10/5/16, 7:29 AM|
|10/9/16, 11:18 PM|
|10/9/16, 4:58 PM|
|10/9/16, 8:16 AM|
|9/29/16, 11:33 PM|
|9/29/16, 4:28 PM|
|9/29/16, 8:41 AM|

The format is date. If I had a way to reverse the data before graphing, that would accomplish the same thing as a correct sort.

#4

> In Excel, I format as time, not date, and it works. In Calc, formatting
> as time does not work
> Code:
> --------------------
> Date and Time
> 10/24/16, 8:48 AM
> 10/20/16, 11:16 PM

The format that Calc understands by default seems to be
DD/MM/YY HH:MM
or
DD/MM/YYYY HH:MM:SS

So to do what you want you need to change the format code.
You find that under Format>Cells then choose “Date” or “Time” and look at
the Format Code entry (try adding a comma between the date and time, but
also note that you had leading spaces in your list).
That will probably generate a user-defined format, or alternative take the
comma out of your list.
Hope it helps

-G-

#5

I’ve had this issue years ago. IIRC I had to set the language in the Properties diaglogue for a (range of) cell(s) to US English to get around the AM/PM issue. Dutch doesn’t know this format.

#6

Thank you for the tips. It took me a long time to respond as I was out of town for daughter’s surgery. I will try both suggestions when I next update.