Sep 18 2019 03:51 PM
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!
Sep 19 2019 01:46 AM
Sep 19 2019 07:43 AM - edited Sep 19 2019 07:44 AM
SolutionYes, 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.
Sep 20 2019 02:45 AM
Sep 20 2019 05:48 AM
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.
Sep 23 2019 02:13 AM
Sep 26 2019 10:10 AM
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?
Sep 26 2019 10:46 AM
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.
Sep 26 2019 02:22 PM
Thank you so much, @Sergei Baklan.
Sep 27 2019 11:15 AM
@dwanajw , you are welcome
Sep 19 2019 07:43 AM - edited Sep 19 2019 07:44 AM
SolutionYes, 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.