Date formatting problem
 

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.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=374124

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


--
eluehmann
------------------------------------------------------------------------
eluehmann's Profile: http://www.excelforum.com/member.php...o&userid=13095
View this thread: http://www.excelforum.com/showthread...hreadid=374124

On Thu, 26 May 2005 06:16:15 -0500, greg7468
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

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

On Thu, 7 Jul 2005 13:33:09 -0700, Abhijeet Bagade
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