Cant change timestamp from MM/DD/YYYY HH:MM:SS to MM/DD/YYYY

[redlance]

I am working with about 4 million rows of data all the dates for each record are timestamped down to the second.

The problem is that although I can show the date in Power Pivot without the hours/minutes/seconds, when I create a pivot table, excel still tries to pull in the entire time stamp. This prevents grouping because it gives off an error when trying to add the date field to the row labels.

As stated above, I can correctly get the hours, minutes, seconds to filter out in the display of the cell but its source still reports the entire stamp.

After creating a pivot table and trying to drop the date field into row lables I get this error (because there are too many rows)

If I try to put the date field into filter that also causes an error because their are too many records caused by the granularity of the dates

This isn't a problem in excel with less than 1 million rows because I could use text to columns but that option isnt available for power pivot.

So, is there a way that I can get excel not to pull in the entire timestamp when creating the pivot table?

 

[barnettjacob]

Your fundamental problem is that each date is actually different and in PowerPivot pivots there is no way to use the 'group by' function available normally.

The answer is either:
- a calculated column in your table that shortens the date (think you can use the round down function for this)
- depending on your data source if its an SQL query then you can convert the date as it is imported to PowerPivot

Option 1 might be the simplest and quickest way to deploy the change but a calculated column on a large table could cause performance issues, especially if it is used to connect to a separate date table (which btw you should be using). Rob Collie would certainly advocate an adjustment to the source data.

Hope this helps.
Jacob

 

[redlance]

Thanks for the response. I will try rounding down.

Any other ideas are welcome as well.