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

[VirtualMechanic]

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.

[VirtualMechanic]

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.

[mikekaganski]

For cases where the data is already imported as text, there is a dedicated data type conversion tool: Data->Text to Columns. And note that it has column titles right-clickable, giving a useful context menu (just like CSV import dialog).

[mikekaganski]

Put cursor to any datetime cell in the pivot table; Data→Group and Outline→Group... (F12). Select Group by→Intervals:→Days.

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

Ref.: ProLibreOffice: Фильтрация по месяцам и годам в сводной таблице Calc (Rus).

[VirtualMechanic]

Thank you!

[mariosv]

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.

[VirtualMechanic]

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.