Thinks all my dates are text for sorting

masplin

Active Member
Joined
May 10, 2010
Messages
413
Powerpivot seems great for handling the data but the functionlity seems awful. I might give up with this, but hopefully I'm just being stupid. I have a column called "registration Date". Powerpivot has it as a date and I have formated it mmm-yy. So far so good.

If I create a pivot table using this field I have 2 problems
1. The date format is dd/mmm/yy. There is no function as in pivot to change the display of these units and selecting the cells and doing format cells makes no difference. I seem to be stuck with an unreadable date format.
2. Possible related, but when I go to sort it only has A to Z when I was expecting oldest to newest, as they are dates. If I sort it puts Mar11 next to Mar12.

My conconclusion is that for some reason it thinks these date fields are text so can't format them or sort them. Now becoming a useless tool quite quickly.

Thanks for any advice

Mike
 
Honestly I still think you should use the sortby column feature rather than manual. Manual sucks :)

There is one other approach to try as well: on the Pivot Options ribbon tab in Excel, click on Fields, Items, and Sets | Create Set Based on Row Items - this will let you create a set of dates that are manually sorted the way you want, and then re-use that set anywhere in the workbook.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top