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.

[eluehmann]

I would insert a column and put in the formula =month(a1)

This will give you the month for your pivot table and you can group by that

[Ron Rosenfeld]

On Thu, 26 May 2005 06:16:15 -0500, greg7468
<greg7468.1pnqrp_1117142136.702@excelforum-nospam.com> wrote:

>
>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

[Abhijeet Bagade]

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]

On Thu, 7 Jul 2005 13:33:09 -0700, Abhijeet Bagade
<AbhijeetBagade@discussions.microsoft.com> wrote:

>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