Pivot Table Showing Time Incorrectly

[King of Ka]

Hello,

I'm having a problem with a pivot table which is completely baffling me. I have data which I'm looking to use a Pivot Table to allow me to quickly filter and use for calculations. One of the columns my data has is a datetime value on 15 minute intervals. If I use the this datetime value (in the Time column), my pivot table works exactly as I want.

pivot.png

However, the problem arises because some of my data (not included in the attached file) is not from the same date as the other data. Therefore, I want to be able to basically scrub out the date and just have time (eg so that 7:00 AM on 12/1 would be counted the same as 7:00 AM on 12/2). To do that, I added a column (Period) which calcs a time value from the Time column. But now when I add Period as my Rows, Excel combines all the data based solely on the minute value, so I end up with four categories, ":00", ":15", ":30", and ":45". I don't seem to be able to reformat these numbers (ie if I set it to Number it still just shows ":00") and I cannot figure out why Excel is formatting this way.

Apologies for the large attached file.

[XLent]

when you add the Period field the Pivot is grouping -- if you right click and ungroup you will get your time values.

so, further to the above, pending your own personal preference (and PT layout) you could dispense with "Period" field and right-click Group your "Time" field by Hours & Minutes.