Pivot Table - Time

Hi All,
I need to report on times logged for incidents logged on our helpdesk system.
Format from the system is 01:12:00 hh:mm:ss.

When creating the pivot table to see total time spent on all incidents, I am trying to SUM the times together for overall totals. So if Admin1 spent 00:30:00 on incident 1234, and 1:15:00 on incident 2345, the total should read 1:45:00.
However, the result is altogether blank, not even an incorrect result.
Values do pull through. If I move the time value to the Column Fields, they are all displayed individually.

Any ideas as to how to get this right?

Cheers
Ian

I don’t know how your data are structure before you created the pivot table and assumed that you have such a structure:
image description

The time format is hh:mm:ss

Next I created the following pivot table:
image description

To the result looks correct.

If this is not the answer you need, please specify how your data are structured and what exactly you do when you try to create the pivot table.

Right. So it seems to be a formatting issue.
In the CSV, the cells format starts as text.
Changing the format to Time format [HH]:MM:SS adds a leading apostrophe, which (as I understand it) reverts the field back to text (a la Excel).
So Calc can’t SUM text fields, I get a blank result. If I remove the apostrophe, all adds up nicely. But manually removing 618 apostrophes is a tad inefficient :slight_smile:

Any ideas?

@onegreenparker - I did not know that you are coming from a CSV file. In this case you first need to make sure that you have a time formats which can be used in Calc calculation. Please follow the advice of @mariosv and report your steps and results to enable further advice.

Try selecting marking detect special numbers and if it doesn’t work, for the column with times select a date type in the import CSV set up window.
If you can’t in this way, please reproduce a CSV line here (mask any non revealable information) to do a try.