Date formatting problem

[greg7468]

Hi all,
I have a column of imported dates in the dd/mmm/yy format.

I need to create a pivot chart of dates but ideally need it combined
into totals for months rather than single days.

I have tried formatting and pre-formatting the dates into mmm/yy to no
avail, when I click on the cell it always comes back 01/01/05.

I have even tried text to columns, splitting the orinal date into 2
columns
dd/ and mmm/yy. But when I click on the second column the formula bar
still shows 01/01/05 and the pivot chart of this column still sees them
as seperate days not combined into months.

Can anyone help please.

Thanks,

Greg.

 

[Ron Rosenfeld]

Hi all,
I have a column of imported dates in the dd/mmm/yy format.

I need to create a pivot chart of dates but ideally need it combined
into totals for months rather than single days.

I have tried formatting and pre-formatting the dates into mmm/yy to no
avail, when I click on the cell it always comes back 01/01/05.

I have even tried text to columns, splitting the orinal date into 2
columns
dd/ and mmm/yy. But when I click on the second column the formula bar
still shows 01/01/05 and the pivot chart of this column still sees them
as seperate days not combined into months.

Can anyone help please.

Thanks,

Greg.

The date will always show as a full date in the formula bar; doesn't matter how
you format the cell. Formatting only changes what the cell displays, not what
it contains.

However, pivot tables allow you to group by months.

I am assuming that your dates are real Excel dates and not text strings.

Assuming your dates are in the column area of the pivot table; right click
someplace in this area, and from the menu that opens select "Group and Show
Detail". Then select Group. The dialog box that opens will give you the
opportunity to group by Months.


--ron

 

[Guest]

Hi Ron,

Stmbled on this conversation as I am facing the same problem. When I tried
your suggestion, I get an error - "Cannot Group that Selection"
What am I missing?
Looking forward to hearing from you.

Regards

 

[Ron Rosenfeld]

Hi Ron,

Stmbled on this conversation as I am facing the same problem. When I tried
your suggestion, I get an error - "Cannot Group that Selection"
What am I missing?
Looking forward to hearing from you.

Regards

Are your dates in the column area?
Are your dates true Excel dates or are they text representations?

Are you clicking in the column area?


--ron