Thinks all my dates are text for sorting

masplin

Active Member
Joined
May 10, 2010
Messages
406
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
 

powerpivotpro

Board Regular
Joined
Jan 18, 2012
Messages
242
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,560
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top