Forum Discussion
Pivot Table Values not matching Source values (Max Date)
My pivot table is changing the value of the source data to a different number, even though the cells are formatted exactly the same.
I have a table containing dates of calls with timestamps for individual cases with unique ID's. I have formatted the date/time data to Number values, with 10 decimal places to get the full number without rounding. I created a pivot table pulling the max date value for each ID from these fields. It's important that the number result include the timestamp, because there are some ID's with multiple calls on the same day, and I need the very latest call. The results of the max call date (number) in the pivot table for the exact call in the table from which the data is being pulled is a different number. See below example:
Source info value: 7/3/18 4:48 PM (m/d/yy 12:00 am)
Result when formatted as Number: 43284.7004137731
Result when max date calculated in pivot table: 43284.7004166667
I've done thorough troubleshooting from the formatting angle, so I know it's not a formatting selection issue. For example, I opened the data results from the pivot table, and the data is formatted as "mm/dd/7777 24:00" so I tried re-formatting the original data to the 8-digit, military time format but it did not impact the original number value result. I can confirm this because in my source data table I am concatenating the date value with the case ID's in a separate field to create their unique ID's to perform a final search. So for example, the above is "ABCDEFG43284.7004137731" and even after changing the formatting to match the results value from the pivot table, the number value did not change.
Please help!
Yes, it works like this
Actually your timestamp has seconds and milliseconds. If add data to data model creating Pivot Table you have correct result. If not - Pivot Table rounds milliseconds.
9 Replies
Yes, it works like this
Actually your timestamp has seconds and milliseconds. If add data to data model creating Pivot Table you have correct result. If not - Pivot Table rounds milliseconds.
Thank you for explaining why it's happening. So How can I add the millisecond value to the Pivot table calculations? OR remove the millisecond value from the source data before creating the pivot table?
If Pivot Table add data to data model creating Pivot Table:
If rounding source data, you may add one more column with formula
=MROUND([@A],"00:00:01")That's a column C in this sample
and pivot your data based on this column.
- Hi Sergey, I didn't know that.
Funny enough, if you change the format of the source data to general and then create a normal pivottable it does get the correct precision.Hi Jan Karel,
It also rounds in formula bar
That's only formatting, but if hit enter in formula bar milliseconds will be rounded. Perhaps same story with some other operations, don't know.
- Can you post a workbook with just the ID's and dates and a pivot based on those two?
