2

I have a CSV file as shown below that contains time durations. When I import into Calc, it looks good.

However, when I want to work with the time duration and manipulate them, it doesn't work. When I try to convert the duration into the following Time format : HH:MM:SS It seems that it converts my data and adds an extra apostrophe in front of it.

is that a bug ? is there a way to fix that ?

enter image description here

4
  • 1
    How exactly are you importing the file? it sounds like the column is being identified as text - you may need to identify it explicitly as a datetime. See Text import - Detect special numbers Commented Jun 9, 2017 at 15:21
  • I am importing the columns as "Standard". From the documentation in your link, it seems that LibreOffice determines the type during import
    – Frederic
    Commented Jun 9, 2017 at 16:01
  • Next time please put the CSV contents in plain text rather than a screen capture. I had to retype the data from the image.
    – Jim K
    Commented Jun 10, 2017 at 16:07
  • Sure, I'll provide the raw data next time, sorry!
    – Frederic
    Commented Jun 13, 2017 at 14:34

2 Answers 2

3

Import the data as strings, then convert into time values with this formula:

=TIMEVALUE(RIGHT(H2,8))

TIMEVALUE returns a floating point number, which is the underlying value for dates and times. To view it as a time, go to Format -> Cells or press Ctrl+1.

set cell to use a time format

The RIGHT function strips off the Fri part of the string, because these are times, not dates.

Adapted from kumiponi's answer at: https://ask.libreoffice.org/en/question/30713/please-find-me-easy-way-convert-date-string-into-data/

0
2

The same problem was solved here. I have test it - it work.

I copied gif file from there to here:

enter image description here

Explanation:

  1. Select the column
  2. Click Data>Text to columns
  3. In the dialog, click the column
  4. The "Column type" selector ungreys itself, select a date format
4
  • Thanks for this tip but it doesn't work in my case as I have durations (HH:MM:SS) and converting them into D/M/Y converts them from HH:MM:SS into a date (not what I want)
    – Frederic
    Commented Jun 10, 2017 at 14:51
  • Have tried it just now - it work as expected. This trick needed for converting from text to nontext (removing apostrophe in the beginning of cell). Then you can adjust format by the standard way (selecting cells -> right mouse button -> 'Format cells' -> 'time' -> 'HH:MM:SS'.
    – MiniMax
    Commented Jun 10, 2017 at 15:05
  • Also, you can set needed format for cells firstly, then convert text to column. The converted text will be in the specified format, which you have choose at the beginning.
    – MiniMax
    Commented Jun 10, 2017 at 15:15
  • The solution brought by Jim K really works the best for me as I don't want to have to click on anything. A formula is always best !
    – Frederic
    Commented Jun 13, 2017 at 14:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .