Excel 2007 Pivot Table... group by.. date.. problem

[someguy91]

I started using Excel 2007.. and I have a bunch of dates.. and I can't group the freaking dates. I have no idea how to do it because everytime I group.. it does something crazy.

edit.. so I'm trying to group by month.. but there are no options! If I try "grouping" it just gives me another group field!

 

[Datsmart]

This Demo shows how to Group in a Pivot table.
http://www.datapigtechnologies.com/flashfiles/pivot5.html

Don't know if Excel 2007 works the same way.

 

[wvfitz]

Hello,

I am attempting to group the dates but I recieve an error message "Cannot group that selection". The data has no empty cells and is formatted the same in Excel.

Can anyone help me with this?

Thank you.

 

[mrhartley]

Hello,

I am attempting to group the dates but I recieve an error message "Cannot group that selection". The data has no empty cells and is formatted the same in Excel.

Can anyone help me with this?

Thank you.

Hello, from what I remember you will also need to make sure the data your grouping is only numeric or a real date, or at least something like that.

Anything that the grouping function does not like will stop it in its tracks and it is tends to be down to incorrect data.

~Mark

 

[Andrew Poulsom]

Sounds like:

http://www.contextures.com/xlPivot07.html#Problems

 

[wvfitz]

I reformatted and that took care of it.

Thank you for your help!

 

[El_Conde]

I am having issues when trying group data by date on a pivot table. Here is what I got. I have spreadsheet with all my orders and I want to be able to group the total amount of items sold by day. On my data sheet I have dozens of lines per day and on my pivot table I want just one line per day with the sum of all the items on the quantity column.

The issue is that when I group on the date field it will convert the date to text and will make it using the format of d-mmm (5-Feb) and because (I think it is) text when I do the auto sort it starts with 1-Apr; 1-Feb... etc I tried changing the Format of the PivotTable field, tried changing the format of the cells and nothing.

Any help?

Thanks

 

[Datsmart]

when I group on the date field it will convert the date to text and will make it using the format of d-mmm (5-Feb)

If Excel is "converting" your dates to the "d-mmm" Format, then it is not Text. Text can not be changed to different Date Formats, only true dates can do that.
When you un-group your Date field, it will change to the default Date Format you have set on your PC under "Regional Settings" in your Control Panel. You must be using "d-mmm" there.

 

[El_Conde]

John, Thanks for the reply. I went to my Regional Settings and nothing; my short date is 'm/d/yyyy' and my long is 'dddd, mmmm d, yyyy'. What is really weird is that when I ungroup it will return the date values with whatever format I select on the Field Format (from the Pivot Table not the Cell Format; the latter one doesn't seem to affect the cells whether group or ungrouped). But when I group again it reverts back to the 'd-mmm'. In all honesty, I could care less about the format but the real issue is that it sorts using a "text like" approach (1-Apr, 1-Feb, 1- Dec, 1-May, 2-Apr, 2-Feb, etc). That's what leads me to think that it somewhat interprets the date as text.

I am not sure if this sheds more light but, the reason of my Date grouping by Day is because my date fields contain the time of transaction and I just want the summarized level on my Pivot Table. Also, I am using the Auto Sorting of the Pivot Table field.

Thanks,
-Eddie

If Excel is "converting" your dates to the "d-mmm" Format, then it is not Text. Text can not be changed to different Date Formats, only true dates can do that.
When you un-group your Date field, it will change to the default Date Format you have set on your PC under "Regional Settings" in your Control Panel. You must be using "d-mmm" there.

 

[tictac]

Hello all, I am having trouble with the "group field" function in excel 2007.

I am trying to group data in a pivot table into quarters and cannot figure out how to do it.

The "group field" button is grayed-out (though the group and ungroup functions are available).

As far as I can tell, I am doing the right steps-- and I am working with dates which are in proper format (i.e. mm/dd/yyyy). Excel is recognizing them as dates not text. Is there something I must do before trying to group the fields?


many thanks!

tic tac