Excel, Pivot table and total time formatting

[QMYork]

I am working a with a large spreadsheet that deals with elapsed or total time (e.g., 2:45:30 means 2 hours 45 minutes and 30 seconds NOT the actual time of day; 2:45 and 30 seconds AM) My column is formatted for TOTAL time but when I create a pivot table (PT) from the data the formatting is automatically changed to the time of day format where 1:30 is considered to be 30 minutes after 1 am. I can't get the correct formatting to transfer to the PT nor can I reformat the data in the PT.



Any thoughts?

Advice?

Tips?

 

[GraH]

Maybe like this?

 

[QMYork]

Perfect! That did the trick. I didn't realise I could format the PT different from the original data source.

I also realised I had the "Value field settings" - see image - wrong. When I switched it to "sum" from "count" I was getting more of what I was looking for.
My next quest will be to learn how to "round" the the time. 30 seconds or more pushes the minutes up by one. 30 minutes or more pushes the hour up one...

 

[GraH]

You may try ceiling function in combination with time function.