Changing dates to month and year in Excel for a Pivot table

[Marckas]

I just want to change the values of cells that contains date like "01/14/2009". I wanted to feed the list into a pivot table but I wanted to change the values of the date to just month and year like changing 01/14/2009 to Jan 2009. I know I could use the format cells thing, but when I feed this list to be a pivot table tool, it lists all the dates as a seperate record. I just want to group all the January's date to show as Jan 2009 in the pivot table tool. Please help!!

 

[SkipVought]

Hi,

Right click the Pivot Field containg your DATE.

Select Grou & Show Detail > Group

In the Grouping Window, select MONTH & YEAR

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marckas]

It does not let me select Group. Gives me a message "cannot group that selection".

 

[SkipVought]

That's because you have non-valid values in your date field, such as BLANKS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI: If you can fix your source data in some way such that EVERY row value is a valid date, then here's the drill...
[tt]
1. Fix your source data field containing the numeric data you want to group.

2. Run CleanMyPivots

3. Group your PT field.
[/tt]

Sub CleanMyPivots()
'SkipVought/2007 Apr 30
'--------------------------------------------------
':cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

This is in line with Skip's suggestion....

Your dates in the source data might actually be text that just looks like dates.

Here are a couple of tests to see if that's the case:
1) Are the items in that columns left-justified or right-justified? (Make sure you don't have it forced to left, right or center when you check.) Numbers default to the right and text defaults to the left.

2) Use a formula like
[tab]=isnumber(rngDate)
where rngDate is any cell that contains a date. If it returns FALSE then you know that you don't really have numbers.

If this turns out to be the problem, there are a few tricks to quickly convert that column to real dates.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.