Date formats (2000)

Topic

New Reply

I have a large spreadsheet that includes a column that contains the date a transaction took place (mm/dd/yy). I want to include this col. in a pivot table, but I want the total of Jan-00, Feb-00 to present. Formatting the cells to “mmm/yy” doesn’t work, I still get a column accross the top for each day a transaction took place. I also tried the MONTH and YEAR function to break it down but when I got to the Pivot Table the months went “1…10…11…12…2…3…4…” etc.
How do I get the pivot table to “look” at the date as “mmm/yy” instead of “mm/dd/yy”?

Reply | Quote

Replies

Create a new column in your data that refers to your date column (I will assume it is col D):
This new column should have:
=date(year(d1),month(d1),1)

This will make ALL January dates in 2002 = Jan 1,2002, etc
Do the Pivot table columns with this column and format it “mmm/yy”

Steve

Reply | Quote

Thank you very much! Worked perfectly…I won’t tip my hat to you because you might see the bald spot from where I was pulling my hair out.

Stats

Reply | Quote

Yet another approach (which we use where I work) is:

1. Create new column.
2. If your new column is E, E1 contains formula “=D1” (without the quotes).
3. Format the new column as “mmm/yy”.
4. Copy–> Edit Paste Special–> Values.

You’ll get the same result for your pivot table.

Finally, we also use VBA code to be sure the months appear in the correct order. Here’s a sample of our code (keeping in mind that we call this field “Month” and we use only the first three letters of the month without the year):

On Error Resume Next
With .PivotFields(“Month”)
.PivotItems(“Jan”).Position = 1
.PivotItems(“Feb”).Position = 2
.PivotItems(“Mar”).Position = 3
.PivotItems(“Apr”).Position = 4
.PivotItems(“May”).Position = 5
.PivotItems(“Jun”).Position = 6
.PivotItems(“Jul”).Position = 7
.PivotItems(“Aug”).Position = 8
.PivotItems(“Sep”).Position = 9
.PivotItems(“Oct”).Position = 10
.PivotItems(“Nov”).Position = 11
.PivotItems(“Dec”).Position = 12
End With
On Error GoTo 0

We need the error trapping because not all of our reports contain all the months.

Hope this helps.

Regards,

Reply | Quote