Date data changes when I create pivot table

[koochandkai]

I'm using a pivot table to rearrange the data in a spreadsheet. When I do this, the info in the date field changes. I'm assuming I'm missing something simple in what I'm doing, but I am clueless as to what it may be. Insight from someone would be very much appreciated.
I'm attaching a spreadsheet with an example. In the original data, all events are in 2015. The second worksheet shows how I created the pivot table. I copied and pasted as values, the info from the pivot table into the third worksheet and then formatted the dates as a custom format.
Many thanks.

[loginjmor]

Hi -

I'm not sure if this is the issue, but your pivot table sums the times for each match of event ID and event type. So Excel treats dates as numbers so it's adding the numerical values of the dates which will come up with some weird results when you have multiple matches. If you want a count of how many times an event occurs on a given date, then change the operation from sum to count.

Is that the problem you are having?

[oeldere]

just format the values in the pivot table to

dd-mm-yyyy hh:mm:ss

[koochandkai]

I really don't want to perform any mathematical calculation -- I am using the pivot table only to rearrange the data in the table so that the data for each Event Type is in the same column. Any other thoughts? Big thanks!

[koochandkai]

Thanks -- I tried that and the values in the pivot table now appear as dates and times -- but still not always the correct ones.

[loginjmor]

Hi -

OK - if you want each time listed in the columns, drag the Time variable down into the ROWS area of the pivot table underneath the Event ID variable.

[koochandkai]

I think I have to put the time data in the values section in order for it to appear in the correct column. (Unless I missed the boat on what you suggested.) This said, when I did what you suggested I found that the date info is correct if I add it as rows -- so the problem is not in some quirk in the times that isn't visible, but in how I'm using the pivot table.

[loginjmor]

Yes - you need the time data in both the data section and in the rows section. Since each time is a unique value, putting the time data in the rows section will segregate each entry without adding them together (which is why you were getting weird dates like year 2045). Then you can do a little cleanup like suppressing any summary data for each category and suppress the grand total at the bottom. Format the time data in the pivot table as suggested by Oeldere above and you have it!

Let me know if you need anymore help on this.

[koochandkai]

Would it be possible for you to attach a spreadsheet with this? Thanks.

[loginjmor]

Sure! See attached. Let me know if that's not what you were looking for.

[koochandkai]

Ahhhh.... Now I'm with you! Huge thanks for all your help.

[loginjmor]

Thanks for the bump! If you are satisfied with the result, please don't forget to edit the thread title to add the word SOLVED per the directions at the bottom of my post.