Excel PivotTable sort of dates

[Guest]

I have used pivot tables quite a bit in the last year.
I have a pivot table with dates and can't get the sorting to work the way I
would like. I want it to sort in ascending or descending order, but it is
sorting like this:
9/19/2005
9/2/2005
9/20/2005
9/21/2005
9/23/2005
9/3/2005
9/4/2005
I have tried formatting the data source, and it is formatted using the
mm/dd/yyyy format.
Can anyone help? I would even be willing to send you the excel workbook if
needed.

Thanks. Todd

 

[Debra Dalgleish]

Excel is probably seeing the dates in the source table as text. There
are some techniques here that you can use to convert the text to dates,
so they can be sorted correctly:

http://www.contextures.com/xlDataEntry03.html

 

[Guest]

Hello Debra,

Me on the other hand, I try not to sort the data entries when crearing a
pivot table, leaving it as in the data source. The option that enables that
is inactive in 'Advanced' table field properties. How can I enable it and use
it to keep the oryginal order of entries?
Thanks, best regards,
Lukasz

 

[Guest]

I am responding to an old post about problems with dates that sort like they
are text. I too have gone back to the source and reformated as dates and
tried to rerun the pivot tables. I even reran them without using a previous
table so the formatting wouldn't carry over.

I also tried the edit replace suggestion (from Contextures), using "/" in
the Find and "/" with the replace and it states that it didn't find the
records with "/".

Once I get the dates sorted, I wanted to group the dates in 3 month
increments, significantly reducing the number of columns in my table. Any
ideas that don't involve VBA would be appreciated.

Ken

 

[Debra Dalgleish]

What do the dates in the source data look like?
Select one of the dates, and choose Format>Cells, and select a different
date format. Does the format of the date in the cell change?

 

[Guest]

The dates look like m/dd/yyyy. When I try to format them nothing happens.
Also, there were two date columns in the source spreadsheet. One column
contained some cells that didn't have a date in them. So, I created a
formula cell that said that if one date was greater than the other, then use
the greatest on, if not use the other. The calculation worked, displaying
dates in each cell. This made me think that they weren't formatted as text,
as text shouldn't return a result. I even tried to use this column as my
source and formatted them all as mm/dd/yy. I still get the same results. I
already tried not using a previous pivot table creation, because it already
returned the wrong results. Any ideas on what to try next?

 

[Roger Govier]

HI

Just formatting the column after data has been entered as text values will
not change the data.
Try highlighting the column>Data>Text to Columns>Next>Next>Date dropdown
select M/D/Y>Finish

See if that solves the problem. If not you can email the file directly to me
and I will take a look
send to
roger at technology4u dot co dot uk
Do the obvious thing with at and dots.

 

[Debra Dalgleish]

An IF formula, such as: =IF(C2>B2,C2,B2)
would work with text or numbers, so that's not a reliable test to
determine if a cell contains text or numbers.

You could try the following on the column of dates:
Select the cells that contain dates
Choose Data>Text to Columns
Click Next twice, to go to Step 3
For Column Data Format, select Date, and in the dropdown, select the
option that matches the month, day and year order in your dates, e.g. MDY.
Click Finish

 

[Guest]

Debra and Roger - Thanks! I did go back to the cells and sure enough they
had a ' in front of them. Last night I kept trying to get into my ebook:
"Excel Pivot Tables Recipe Book - A Problem-Solution Approach" written by
Debra Dalgleish herself. I kept searching for the password to open it and
finally located it on the Apress website. In her book, it mentioned "Text to
Columns" solution, which I just tried before reading your replies, in which
you are both right! This worked and now, all is well. I still would like to
put in a good word rfor Debra's book. An excellent resource! - Ken

 

[Debra Dalgleish]

Thanks for letting us know how you solved the problem, and I'm glad you
like the book!