correct order of months in pivot table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My worksheet has dates in the "mmddyyyy" format with associated counts of
procedures. I would like to present this in a pivot table with the counts
broken down by months and formatted as "Jan, Feb, etc."
How to do it?
Tx
Rocky
 
Convert it to real dates first, select the column with dates, do data>text
to column, click next twice, in step 3 under column data format select date
and MDY

Now apply a pivot table. If you type in 12142006 and thinks it is the same
as 12/14/06 then you are mistaken, Excel needs to know that it is a date you
enter and that can only be done by using date delimiters (or VBA code),
however by using data>text to columns the dates will be converted
 
Peo,
I don't know what you mean by data>text to column;
I think that it is a real date already, just formatted mm/dd/yyyy;
where is it that I am clicking next twice in step 3?;
Tx
Rocky
 
Have you created the pivot table? If your column of data has only dates in it,
Excel should automatically group them by months. What part of the pivot table
are you having problems with?
 
Fred,
Yes, I did create a pivot table and then tried to group them by months, but
got an error message that it could not do so.
Part of the problem could be the date field. I have trouble discerning what
the field is truly formatted as and if in fact Excel sees it as text or date.
Any tips on that would be helpful.
Tx
Rocky
 
Hi Rocky

If you get an error message when trying to Group dates in a PT it means
that there is Text in one or more of the cells in that field, OR there
are blank entries.

It could be that you have defined your range for the source for the PT
to be larger than the current data range, hence you will have blank rows
at the end. Try in the first instance, limiting the range to the used
range where dates exists and see if you can group.

Post back with what version of XL are you using, and we can give you the
best method of defining a dynamic range that will grow as you append
more data to the end of your list.
 
Back
Top