Results 1 to 6 of 6

Thread: Libre Office Calc

  1. #1
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,113

    Default Libre Office Calc

    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?
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

  2. #2
    Join Date
    Jun 2008
    Location
    Podunk
    Posts
    26,341
    Blog Entries
    15

    Default Re: Libre Office Calc

    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)?
    Cheers Malcolm °¿° SUSE Knowledge Partner (Linux Counter #276890)
    SUSE SLE, openSUSE Leap/Tumbleweed (x86_64) | GNOME DE
    If you find this post helpful and are logged into the web interface,
    please show your appreciation and click on the star below... Thanks!

  3. #3
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,113

    Default Re: Libre Office Calc

    Quote Originally Posted by malcolmlewis View Post
    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)?
    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
    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
    Code:
        
    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.
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

  4. #4
    -G- NNTP User

    Default Re: Libre Office Calc

    > 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. #5
    Join Date
    Jun 2008
    Location
    Groningen, Netherlands
    Posts
    19,592
    Blog Entries
    14

    Default Re: Libre Office Calc

    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.
    ° Appreciate my reply? Click the star and let me know why.

    ° Perfection is not gonna happen. No way.

    https://en.opensuse.org/openSUSE:Board#Members
    http://en.opensuse.org/User:Knurpht
    http://nl.opensuse.org/Gebruiker:Knurpht

  6. #6
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,113

    Default Re: Libre Office Calc

    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.
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •