# Pivot table - date/time not compiling and avg time (hh:mm:ss) showing empty

I am trying to create a fairly simply pivot table to quickly compile and break down some numbers from our ticketing system. The original file format was .CSV, which I saved as .ODS to edit and what not.

I set the format of the data fields as I would like them (eg. open date = DD-MM-YYYY HH:MM:SS).

Pivot table settings:

Example data:

The result:

The desired result:

Meaning an overview per day of the average response time.

I cannot get this to work for the live of me. I have used the same function in Excel, which worked fine (with the same source data) but somehow can't get it to work in LibreOffice Calc.

edit retag close merge delete

Sort by » oldest newest most voted

By the data looks date values are imported as text, so when opening/importing csv set up the column to a date type so it is imported properly.

After create the pivot table go to any cell on date column and with [F12] or Menu/Data/Group & Outline/Group you can select the unit.

more

Thank you very much! I found an even easier way to get the date/time format right, which was ticking the box 'Detect sepcial numbers' when loading in the .CSV. This did the trick for me.

( 2018-11-17 00:03:40 +0200 )edit

Put cursor to any datetime cell in the pivot table; DataGroup and OutlineGroup... (F12). Select Group byIntervals:Days.

This requires that date column is in Row Fields: box in pivot table layout.

Ref.: http://antilibreoffice.blogspot.com/2... (Rus).

more

Thank you!

( 2018-11-17 00:06:21 +0200 )edit

I parcially resolved the issue. I got the data to show in the Response Time by loading in a fresh .CSV and made sure the 'string delimeter' was empty. For more info, see this FAQ. This did not help me directly, but sent me on the right path.

Non-the-less it still does not compile it per day, but shows all date/time in a list as show above in 'The result' image.

Edit 1: The only way I seem to be able to get the date/time column working as intended is to create an extra column, which only contains a date, not a time.

I do this by inserting 2 columns on the right side of the date/time column. Then copying the date/time column to one column to the right, and splitting it with 'text to column' to split the time to another column. After this I delete the time column I created and use the new 'date' column instead of the original one.

Is there a faster/smarter way?

Edit 2: I found an even easier way to get the date/time format right, ticking the box 'Detect sepcial numbers' when loading in the .CSV. This did the trick for me.

more