My date formats changed

[Amin]

hi may i know how to solve this proble, i created a table and a column had
date format of jan1st to jan 31st. i used this data in creating a pivot table
and i moved the field name to report filter so that i could filter on which
date o day i wanted after doing this i realised that my dates on the report
filter aren't sorted correctly, i had 10th on the top and further down the
lower numbers how do i get this right to start from 1st to the last day of
the month thanks to all. office 2007

 

[FSt1]

hi
guessing here but Jan 31st may not be a date fomat that excel can
recognize. 2003 doesn't recognize it as a date, not sure about 2007.
you might try a different format that excel can recognize as a date.

regards
FSt1

 

[Ron Rosenfeld]

hi may i know how to solve this proble, i created a table and a column had
date format of jan1st to jan 31st. i used this data in creating a pivot table
and i moved the field name to report filter so that i could filter on which
date o day i wanted after doing this i realised that my dates on the report
filter aren't sorted correctly, i had 10th on the top and further down the
lower numbers how do i get this right to start from 1st to the last day of
the month thanks to all. office 2007

It sounds as if your dates are not really "excel" dates but rather text
representations of the date.

There is no built-in Excel format to produce ordinal numbers (although it could
be done using VBA macros).

What is really in the original column of dates that you are using (the column
that you write has the date format of 1st, etc)? Click on a cell in that
column and see what is in the formula bar.

If the contents of the formula bar also says jan1st or jan 31st, then it is
text and cannot be understood as a date by the pivot table wizard.

If the contents is a TEXT formula that refers to the column where the dates
really are, then you may be able to refer to that column, instead of the
"formatted" column, for your pivot table.

--ron

 

[Shane Devenshire]

Hi,

Excel pivot table will show the dates in the correct order provided the
dates were entered as Excel legal dates. Jan 1st is not a legal date, it
should be entered as 1/1 and then formatted as mmm-d or mmm d. With any of
these formats the dates will not show the "st". But the pivot table field
will display in the correct order.

This is true for all versions of Excel and for all the fields (row, column
or page).