Ask Your Question

Pivot Table - Time [closed]

asked 2013-12-18 12:49:38 +0200

onegreenparker gravatar image

updated 2013-12-18 13:07:34 +0200

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-16 17:35:07.760833

2 Answers

Sort by » oldest newest most voted

answered 2013-12-18 13:11:01 +0200

ROSt52 gravatar image

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.

edit flag offensive delete link more


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 :)

Any ideas?

onegreenparker gravatar imageonegreenparker ( 2013-12-18 13:38:14 +0200 )edit

@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.

ROSt52 gravatar imageROSt52 ( 2013-12-19 13:47:13 +0200 )edit

answered 2013-12-18 19:34:21 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-12-18 12:49:38 +0200

Seen: 2,184 times

Last updated: Dec 18 '13